SQL UNION 1

[jasonwcm]

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???


Cheers,

Jason

 

[swampBoogie]

What's the problem?

 

[r937]

the problem is "The third condition involves both table1 and table2"

it appears to involve only table1

in any case, it is invalid where it is

move it up inside the first subselect in the union

rudy

http://r937.com/

SQL Consulting

 

[jasonwcm]

Thanks r937!

It works perfectly fine now.

Cheers.