Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2013
    Posts
    16

    Unanswered: Combining 2 SELECT Statements (with JOINs) into 1

    Hey all,

    I'm looking for a way to combine the following 2 sets of code into one select statement. They're similar in that they reference the same tables, but they have different conditionals:

    Code:
    SELECT TABLE_01.Date
         , TABLE_01.State
         , TABLE_01.City
         , ISNULL((SUM(A.Bandwidth)),0) AS SD_Bandwidth
    INTO TABLE_FINAL_01
    FROM TABLE_01
    LEFT OUTER JOIN TABLE_02 A
    	ON A.State = TABLE_01.State
    	AND A.City = TABLE_01.City
            AND A.Bitrate = 'SD'
    GROUP BY 
            TABLE_01.Date
          , TABLE_01.State
          , TABLE_01.City
    Code:
    SELECT TABLE_01.Date
         , TABLE_01.State
         , TABLE_01.City
         , ISNULL((SUM(B.Bandwidth)),0) AS HD_Bandwidth
    INTO TABLE_FINAL_02
    FROM TABLE_01
    LEFT OUTER JOIN TABLE_02 B
    	ON B.State = TABLE_01.State
    	AND B.City = TABLE_01.City
            AND B.Bitrate = 'HD'
    GROUP BY 
            TABLE_01.Date
          , TABLE_01.State
          , TABLE_01.City

    I would've thought I could use the code below, but it doesn't turn out to work correctly:

    Code:
    SELECT TABLE_01.Date
         , TABLE_01.State
         , TABLE_01.City
         , ISNULL((SUM(A.Bandwidth)),0) AS SD_Bandwidth
         , ISNULL((SUM(B.Bandwidth)),0) AS HD_Bandwidth
    INTO TABLE_FINAL_01
    FROM TABLE_01
    LEFT OUTER JOIN TABLE_02 A
    	ON A.State = TABLE_01.State
    	AND A.City = TABLE_01.City
            AND A.Bitrate = 'SD'
    LEFT OUTER JOIN TABLE_02 B
    	ON B.State = TABLE_01.State
    	AND B.City = TABLE_01.City
            AND B.Bitrate = 'HD'
    GROUP BY 
            TABLE_01.Date
          , TABLE_01.State
          , TABLE_01.City
    Thanks!
    Last edited by keennay; 09-17-13 at 16:15.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The problem that I'd guess that you're encountering is that you "lose" the TABLE_02 alias when you add the A and B aliases to it. Without knowing which problems you are having, I have to guess at what might have gone wrong.

    From a relational perspective, the best answer would be:
    Code:
    SELECT
       TABLE_01.Date
    ,  TABLE_01.State
    ,  TABLE_02.City
    ,  TABLE_02.Bitrate
    ,  ISNULL((SUM(TABLE_02.Bandwidth)),0) AS Bandwidth
       INTO TABLE_FINAL_01
       FROM TABLE_01
       LEFT OUTER JOIN TABLE_02
    	  ON A.State = TABLE_01.State
    	  AND A.City = TABLE_01.City
       GROUP BY 
          TABLE_01.Date
    ,     TABLE_01.State
    ,     TABLE_02.City
    ,     TABLE_02.Bitrate
    If you need to preserve the column layout that you've used in the past, then you can use:
    Code:
    SELECT
       TABLE_01.Date
    ,  TABLE_01.State
    ,  TABLE_02.City
    ,  ISNULL((SUM(CASE WHEN 'SD' = TABLE_02.Bitrate THEN TABLE_02.Bandwidth END)),0) AS SD_Bandwidth
    ,  ISNULL((SUM(CASE WHEN 'HD' = TABLE_02.Bitrate THEN TABLE_02.Bandwidth END)),0) AS HD_Bandwidth
       INTO TABLE_FINAL_01
       FROM TABLE_01
       LEFT OUTER JOIN TABLE_02
    	  ON A.State = TABLE_01.State
    	  AND A.City = TABLE_01.City
       GROUP BY 
          TABLE_01.Date
    ,     TABLE_01.State
    ,     TABLE_02.City
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2013
    Posts
    16
    Ooops, there was a typo in my code. I'm sure that made everything confusing.

    The problem I'm having is when I use the 3rd set of code, the SD & HD bitrates are totally off, versus when I run the 2 select statements separately.

    This is what I meant:

    Code:
    SELECT TABLE_01.Date
         , TABLE_01.State
         , TABLE_01.City
         , ISNULL((SUM(A.Bandwidth)),0) AS SD_Bandwidth
    INTO TABLE_FINAL_01
    FROM TABLE_01
    LEFT OUTER JOIN TABLE_02 A
    	ON A.State = TABLE_01.State
    	AND A.City = TABLE_01.City
            AND A.Bitrate = 'SD'
    GROUP BY 
            TABLE_01.Date
          , TABLE_01.State
          , TABLE_01.City
    Code:
    SELECT TABLE_01.Date
         , TABLE_01.State
         , TABLE_01.City
         , ISNULL((SUM(B.Bandwidth)),0) AS HD_Bandwidth
    INTO TABLE_FINAL_02
    FROM TABLE_01
    LEFT OUTER JOIN TABLE_02 B
    	ON B.State = TABLE_01.State
    	AND B.City = TABLE_01.City
            AND B.Bitrate = 'HD'
    GROUP BY 
            TABLE_01.Date
          , TABLE_01.State
          , TABLE_01.City

    And I thought this would've worked:

    Code:
    SELECT TABLE_01.Date
         , TABLE_01.State
         , TABLE_01.City
         , ISNULL((SUM(A.Bandwidth)),0) AS SD_Bandwidth
         , ISNULL((SUM(B.Bandwidth)),0) AS HD_Bandwidth
    INTO TABLE_FINAL_01
    FROM TABLE_01
    LEFT OUTER JOIN TABLE_02 A
    	ON A.State = TABLE_01.State
    	AND A.City = TABLE_01.City
            AND A.Bitrate = 'SD'
    LEFT OUTER JOIN TABLE_02 B
    	ON B.State = TABLE_01.State
    	AND B.City = TABLE_01.City
            AND B.Bitrate = 'HD'
    GROUP BY 
            TABLE_01.Date
          , TABLE_01.State
          , TABLE_01.City

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First check to see if the two code snippets that I posted return the expected results. That will help me understand which of three problems I need to help you to address.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Sep 2013
    Posts
    16
    The below worked. Thanks Pat, much appreciated!

    Quote Originally Posted by Pat Phelan View Post
    If you need to preserve the column layout that you've used in the past, then you can use:
    Code:
    SELECT
       TABLE_01.Date
    ,  TABLE_01.State
    ,  TABLE_02.City
    ,  ISNULL((SUM(CASE WHEN 'SD' = TABLE_02.Bitrate THEN TABLE_02.Bandwidth END)),0) AS SD_Bandwidth
    ,  ISNULL((SUM(CASE WHEN 'HD' = TABLE_02.Bitrate THEN TABLE_02.Bandwidth END)),0) AS HD_Bandwidth
       INTO TABLE_FINAL_01
       FROM TABLE_01
       LEFT OUTER JOIN TABLE_02
    	  ON A.State = TABLE_01.State
    	  AND A.City = TABLE_01.City
       GROUP BY 
          TABLE_01.Date
    ,     TABLE_01.State
    ,     TABLE_02.City
    -PatP

Posting Permissions

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