Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Return all results from Left Table

    I have a table that lists states that I need to see order info from. I am attempting to create a SQL query that displays the production data for those states. So if the state is in my saleState table I want it to show in my query result even if there were 0 sales to that state. Below is the syntax I am trying, but if the count is 0, it is not returning the sales state it is omitting it.

    Code:
    Select a.state, COUNT(b.recordID)
    From saleState a left outer join tblSales b
    On a.state = b.state 
    And b.orderDate between '01/01/2012' AND '12/31/2012'
    Can one of the greats here point out the error in my query syntax?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you post your exact query?
    The one posted is missing [at least] the GROUP BY clause.
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    Sorry I just omitted the group by...the PC that has SQL Server is on a wireless connection and surprise surprise the wireless is not working at the current so I was reading it from one screen and typing it in here...
    Code:
    Select a.state, COUNT(b.recordID)
    From saleState a left outer join tblSales b
    On a.state = b.state 
    And b.orderDate between '01/01/2012' AND '12/31/2012'
    Group By a.state

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Maybe it is late in the day but I don't see anything wrong with your query that would mean you wouldn't get a result for all saleState.state items...
    Code:
    DECLARE @states table (
       state_name char(1)
    )
    
    INSERT INTO @states (state_name)
      VALUES ('a'), ('b'), ('c')
    
    DECLARE @sales table (
       state_name char(1)
    )
    
    INSERT INTO @sales (state_name)
      VALUES ('a'), ('b'), ('b'), ('b'), ('b')
    
    SELECT a.state_name
         , Count(b.state_name)
    FROM   @states As a
     LEFT
      JOIN @sales As b
        ON b.state_name = a.state_name
    GROUP
        BY a.state_name
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2012
    Posts
    188
    I tested your scenario and it returns the data as desired. One thing I just noticed with my query is that it will return the data until I add in the orderDate parameter.

    Should I set-that up a different way?

    EDIT
    my orderDate field is only contained in tblSales, it is not in the other table.
    Last edited by jo15765; 04-16-13 at 13:15.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If it really is part of the ON clause then no, you shouldn't have the problem you described.

    If it's part of a WHERE clause then that's a different story.
    Code:
    DECLARE @states table (
       state_name char(1)
    )
    
    INSERT INTO @states (state_name)
      VALUES ('a'), ('b'), ('c')
    
    DECLARE @sales table (
       state_name char(1)
     , order_date date
    )
    
    INSERT INTO @sales (state_name, order_date)
      VALUES ('a','2013-01-01')
           , ('b','2013-01-01')
           , ('b','2013-01-01')
           , ('b','2013-01-01')
           , ('b','2014-01-01')
    
    SELECT a.state_name
         , Count(b.state_name)
    FROM   @states As a
     LEFT
      JOIN @sales As b
        ON b.state_name = a.state_name
    GROUP
        BY a.state_name
    
    SELECT a.state_name
         , Count(b.state_name)
    FROM   @states As a
     LEFT
      JOIN @sales As b
        ON b.state_name = a.state_name
    WHERE  b.order_date BETWEEN '2012-01-01' AND '2013-01-01'
    GROUP
        BY a.state_name
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2012
    Posts
    188
    This is the syntax that I used...and it is only returning the state_names with a Count, so for example c, d, and e are not returned in my query...
    Code:
    Create Table #Test
    (
    state_name char(1)
    )
    Insert Into #Test (state_name)
    Values ('a')
    Insert Into #Test (state_name)
    Values ('b')
    Insert Into #Test (state_name)
    Values ('c')
    Insert Into #Test (state_name)
    Values ('d')
    Insert Into #Test (state_name)
    Values ('e')
    
    Create Table #Test1
    (
    state_name char(1),
    order_date datetime
    )
    
    Insert Into #Test1 (state_name, order_date)
    Values ('a', '2013-01-01')
    
    Insert Into #Test1 (state_name, order_date)
    Values ('b', '2013-01-11')
    
    Select a.state_name, Count(b.state_name)
    From #Test a
    Left Join #Test1 b
    On b.state_name = a.state_name
    Where b.order_date Between '2013-01-01' And '2013-01-31'
    Group By a.state_name
    EDIT ---
    and if it makes a difference (it may or may not) I am running SQL 2000
    Last edited by jo15765; 04-16-13 at 13:37.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Change the WHERE to AND. Review my previous code posted.
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2012
    Posts
    188
    Changing the where to an and corrected it.

    Now I am curious why did that make a difference in the set of results that were returned?

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The where clause conditions are applied after the join conditions. After the join conditions, the rows you wanted had a null orderdate, and would not pass the where clause.

  11. #11
    Join Date
    Feb 2012
    Posts
    188
    That makes sense. Thank you for explaining that.

  12. #12
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Another trick

    SELECT S.state_code, COUNT(Sales.invoice_nbr)AS invoice_cnt
    FROM (SELECT X.state_code
    FROM (VALUES ('AL', 'AK', .., 'WY')) AS X(state_code)
    ) AS S(state_code)
    LEFT OUTER JOIN
    Sales
    ON S.state_code = Sales.state_code
    AND Sales.order_date
    BETWEEN '2012-01-01' AND '2012-12-31'
    GROUP BY S.state_code;

    The VALUES clause is a table constructor that can be held in main storage for smaller lists of values or expressions. No need to use Tempdb storage and disk access time.

  13. #13
    Join Date
    Feb 2012
    Posts
    188
    So instead of actually creating a table to hold those values (since it is a small list) I could just use the word Value & the values in my SQL statement?

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can use the VALUES constructor to create a rowset within the FROM clause in SQL 2008 and later.

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

  15. #15
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Yep!

    Quote Originally Posted by jo15765 View Post
    So instead of actually creating a table to hold those values (since it is a small list) I could just use the word Value & the values in my SQL statement?
    I got it wrong:

    (VALUES ('AL'), ('AK'), .., ('WY'))) AS X(state_code)) AS S(state_code)

    This builds a one column derived table in main storage with those values. You can save it and do cut&paste. You do not have to access tempdb. Another trick is to replace the constants with expressions. This is the SQL version of the old FORTRAN and Pascal CONSTANT declarations.

Posting Permissions

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