SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2001
    Location
    glasgow
    Posts
    104
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Union Column Type Problem

    I need the values in column 1 to be potentially 'y' or 'n' from the 1st query, then a list of competition ids (integer value) from the 2nd.

    However, witht the following query, I only get one digit of id's greater than 9

    PHP Code:
          $competitionSql 'SELECT DISTINCT stats_iscompetitive, '
                
    "CASE WHEN stats_iscompetitive = 'y' THEN 'all competitive games' "
                
    "ELSE 'all non-competitive games' END "
                
    'FROM stats '
                
    'UNION '
                
    'SELECT DISTINCT compid, compname '
                
    'FROM fix_competition, stats '
                
    'WHERE compid = stats_compid '
                
    'ORDER BY 2 ASC'
    eg, for compid 1, the value returned is 1; for compid 11, the value returned is also 1

    I've tried casting, but unsuccessfully.

    Thanks In Advance,

    Fraser

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that's because the column characteristics are taken from the first (sub)query in the UNION

    try switch the (sub)queries around to put the second one first
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2001
    Location
    glasgow
    Posts
    104
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    that's because the column characteristics are taken from the first (sub)query in the UNION

    try switch the (sub)queries around to put the second one first
    THanks - I need them in the order 'y', 'n', then the id's (ordered by 2nd field) though - in the query preferably, rather than sorting later with php.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT compid
         , compname
         , 1 as sortseq
      FROM fix_competition
         , stats
     WHERE compid = stats_compid '
    UNION 
    SELECT stats_iscompetitive
         , CASE WHEN stats_iscompetitive = 'y' 
                THEN 'all competitive games' 
                ELSE 'all non-competitive games' END
         , 0
      FROM stats
    ORDER 
        BY 3
         , 2 asc
    note that sorting on the 2nd column for the Y/N values works

    also note that i've removed the two DISTINCTs, as the UNION serves the same purpose
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Sep 2001
    Location
    glasgow
    Posts
    104
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again - but now it's returning '0' instead of 'y' or 'n'.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yeah, i had a feeling it would do that

    the result set column is numeric by virtue of the first (sub)query

    okay, try this --

    change SELECT compid

    to SELECT concat('',compid) as id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Sep 2001
    Location
    glasgow
    Posts
    104
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent - thanks!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •