SQL UNION Statement

Hi..

Just wondering if what is wrong with this SQL statement..

(
SELECT mch.ID AS mchID, m.ID AS monoID, m.name, m.artist, mch.dlCode, mch.price, mch.created, mch.last, 'top' AS whQuery FROM tbl_mono m, tbl_monochannel mch
WHERE mch.channelID = 2 AND mch.adID = 1
AND mch.monoID = m.ID
)
UNION
(
SELECT 0, ID, name, artist, 0, 0, NULL, NULL, 'bottom'
FROM tbl_mono
WHERE ID NOT IN
( SELECT monoID FROM tbl_monochannel WHERE channelID = 2 AND adID = 1 )
AND catID IN
(SELECT DISTINCT ID FROM tbl_monocat WHERE channelID = 2) )
AND ( lower(m.name) LIKE '%days%' OR lower(m.artist) LIKE '%days%' OR lower(m.keyword) LIKE '%days%' )
ORDER BY 'top' DESC, m.name ASC

Basically, it's something like this..

( SELECT col1, col2 FROM table1 WHERE (some conditions) )
UNION
( SELECT col3, col4 FROM table2 WHERE (some conditions) )
AND (some conditions )

The third condition involves both table1 and table2.


Any idea anyone???

That's illegal. Union concatenates (and distinctifies) two result sets. You can't add a condition to the end.

If you want to do something like this, wrap the use the result of the Union as a subquery to specify your third set of conditions.

ie.
select col1, col2 from
(
SELECT col1, col2 FROM table1 WHERE (some conditions)
UNION
SELECT col3, col4 FROM table2 WHERE (some conditions)
)
Where (some conditions )

Thanks! Running perfectly fine now..