#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    2
    Rep Power
    0

    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???
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Location
    Los Angeles, CA
    Posts
    21
    Rep Power
    0
    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 )
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    2
    Rep Power
    0
    Thanks! Running perfectly fine now..

IMN logo majestic logo threadwatch logo seochat tools logo