February 9th, 2004, 02:51 AM
SQL UNION Statement
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
SELECT 0, ID, name, artist, 0, 0, NULL, NULL, 'bottom'
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) )
( SELECT col3, col4 FROM table2 WHERE (some conditions) )
AND (some conditions )
The third condition involves both table1 and table2.
Any idea anyone???
February 11th, 2004, 03:37 AM
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.
select col1, col2 from
SELECT col1, col2 FROM table1 WHERE (some conditions)
SELECT col3, col4 FROM table2 WHERE (some conditions)
Where (some conditions )
February 11th, 2004, 05:03 AM
Thanks! Running perfectly fine now..