SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    Non-Member
    Join Date
    Jul 2008
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to combine multiple selects in one query

    Hello,

    I have one table and like to combine multiple select statements in one query.

    tbl_students

    ID Dept
    -------
    1 A
    2 B
    3 A
    4 C
    5 B
    6 D
    7 E
    8 F

    SELECT Dept from tbl_students
    where Dept in ('A', 'B', 'C')

    SELECT Dept AS Dept2 from tbl_students
    where Dept in ('D', 'E')

    SELECT Dept AS Dept3 from tbl_students
    where Dept in ('F')

    How do I join these queries in one query to list the values in 3 columns as:

    Dept1 Dept2 Dept3
    ---------------------
    A D F
    B E
    C

    Thanks.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You could do something like
    Code:
    SELECT 
        1 AS Deptnumber
      , Dept 
    FROM tbl_students
    WHERE Dept IN ('A', 'B', 'C')
    UNION
    SELECT 
        2 AS DeptNumber
      , Dept 
    FROM tbl_students
    WHERE Dept IN ('D', 'E')
    UNION
    SELECT 
        3 AS Deptnumber
      , Dept
    FROM tbl_students
    WHERE Dept IN ('F')
    And then loop through the result set and elaborate the data according to the value of Deptnumber.

  3. #3
    Non-Member
    Join Date
    Jul 2008
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It worked perfect.

    Thanks.

  4. #4
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Location
    Columbus, OH
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is a great solution for combining queries to produce a single result, and very elegant! I will definitely be using this with frequency.

    Here's a question, though: suppose the number of sub-divisions of the query is indefinite, e.g. when the table contains a column that groups the rows based on a foreign key or some such. In this circumstance, the data to divide the results already exists in the table, but it can change as table data changes.

    Is there a way to do a query like this dynamically, to do UNION / SELECT in a loop based on values in an existing column?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by The Schaef View Post
    Is there a way to do a query like this dynamically, to do UNION / SELECT in a loop based on values in an existing column?
    depends on which database you're using

    MS Access has CROSSTAB queries, which is the exact solution you want

    MS SQL Server has PIVOT queries, but you have to specify all the columns

    i think Oracle has PIVOT as well, but i've never used it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Location
    Columbus, OH
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    ...MS Access...MS SQL Server...Oracle...
    hhmmm... I use MySQL almost exclusively, mostly because of its ubiquitous nature. This could be bad news for me.

  7. #7
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Location
    Columbus, OH
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Never mind. A little digging has revealed I can do pivot tables in MySQL as well. Time to learn a new job skill.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by The Schaef View Post
    A little digging has revealed I can do pivot tables in MySQL as well.
    o rly?

    could you share the resource you found?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Location
    Columbus, OH
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that's hardly wizardry
    Code:
    SELECT
        SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 60 AND CAST(gamedata.Data AS UNSIGNED) <= 87,1,0) ) AS '60-87',
        SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 88 AND CAST(gamedata.Data AS UNSIGNED) <= 115,1,0) ) AS '88-115',
        SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 116 AND CAST(gamedata.Data AS UNSIGNED) <= 143,1,0) ) AS '116-143',
        SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 144 AND CAST(gamedata.Data AS UNSIGNED) <= 171,1,0) ) AS '144-171',
        SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 172 AND CAST(gamedata.Data AS UNSIGNED) <= 199,1,0) ) AS '172-199',
        SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 200 AND CAST(gamedata.Data AS UNSIGNED) <= 227,1,0) ) AS '200-227',
        SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 228 AND CAST(gamedata.Data AS UNSIGNED) <= 255,1,0) ) AS '228-255',
        SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 256 AND CAST(gamedata.Data AS UNSIGNED) <= 283,1,0) ) AS '256-283',
        SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 284 AND CAST(gamedata.Data AS UNSIGNED) <= 311,1,0) ) AS '284-311',
        SUM( IF( CAST(gamedata.Data AS UNSIGNED) >= 312 AND CAST(gamedata.Data AS UNSIGNED) <= 339,1,0) ) AS '312-339'
    FROM ...
    that's just brute force plain old SQL, even cruder than SQL Server's PIVOT syntax

    you have to hardcode for every column that you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    Join Date
    Nov 2008
    Location
    Columbus, OH
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The first article was to solve a specific problem that author had; the second link gives a more detailed walkthrough of how to achieve cross-tabulation. I will still need to tinker with it to see if it can return data other than math calculations, because I would like to output VARCHAR fields that are a sorted list of categories and their subcategories. But it's my understanding that SELECT CONCAT / SUM / IF will generate the SQL code necessary for the pivot table.


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
  •