Results 1 to 13 of 13

Thread: Left Join

  1. #1
    Join Date
    Feb 2012
    Posts
    130

    Left Join

    I am running the below syntax in SQL Server 2000
    Code:
    SELECT case when COUNT(ID) IS NULL Then '0' else Count(ID) end AS COUNTOFID, tbl_one.Ename, tbl_one.EPhone, table2.ID AS tbl2ID
    FROM tbl_one LEFT JOIN table2 ON tbl_one.city = table2.city
    GROUP BY tbl_one.city, table2.ID
    ORDER BY table2.ID ASC
    I want it to show 0 if the count of id is null, and also to show ALL city's in table2 even if they do not exist in table1. I thought a left join was the correct join to use, but it is not showing all city's in table1 nor a 0 if null. Can someone please show me where I made a mistake with the syntax?

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    From which table is the [ID] field you are using in the COUNT(ID) ???
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Feb 2012
    Posts
    130
    The count of id is for table_one.

  4. #4
    Join Date
    Nov 2012
    Location
    Russia. Kursk
    Posts
    3
    may be:

    SELECT count(tbl_one.ID) AS COUNTOFID, table2.ID AS tbl2ID
    FROM tbl_one FULL JOIN table2 ON tbl_one.city = table2.city
    GROUP BY tbl_one.city, table2.ID
    ORDER BY table2.ID ASC

    if I'm not mistaken, 'count' take only 'Not null', so addition conditions aren't necessary.
    For "tbl_one.Ename, tbl_one.EPhone," you need include them in "group by" or to use aggregate functions for them.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,924
    Can you explain in english what you want to accomplish and post the commands needed to build your table1 and table2? The example that you posted doesn't make sense to me, and I can't help if I don't know what you want.

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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jo15765 View Post
    I want it to show 0 if the count of id is null, and also to show ALL city's in table2 even if they do not exist in table1.
    the mistake is that you got your left and right tables mixed up

    try this --
    Code:
    SELECT table2.ID AS tbl2ID
         , tbl_one.Ename
         , tbl_one.EPhone 
         , COUNT(tbl_one.city ) AS COUNTOFID
      FROM table2 
    LEFT OUTER
      JOIN tbl_one
        ON tbl_one.city = table2.city
    GROUP 
        BY table2.ID 
         , tbl_one.Ename
         , tbl_one.EPhone 
    ORDER 
        BY table2.ID ASC
    in a LEFT OUTER JOIN, if you COUNT() a column from the right table, you don't have to check the count
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2012
    Posts
    130
    r937 tring that code also does not display a 0 if the count of the city name in table1 is 0

  8. #8
    Join Date
    Feb 2012
    Posts
    130
    Quote Originally Posted by Pat Phelan View Post
    Can you explain in english what you want to accomplish and post the commands needed to build your table1 and table2? The example that you posted doesn't make sense to me, and I can't help if I don't know what you want.

    -PatP
    I want to join the two tables on city, and get a count of how many records from table one have been shipped to each city in table two.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jo15765 View Post
    I want to join the two tables on city, and get a count of how many records from table one have been shipped to each city in table two.
    and yet you (occasionally?) expect the count of rows in table 1 to be zero?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2012
    Posts
    130
    Yes. Sometimes there will be a city in tabelle two that doesn't exist in table one, in that instance i want sql server to show a 0

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, please read post #7 again -- "if the count of the city name in table1 is 0"

    you're saying the count in table 1 can sometimes be zero

    that doesn't make any sense
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2012
    Posts
    130
    Let me try to explain more...tableone has city's of Mason, Indine, Freemont, Alberta, Rex, Lexintong, Nashville, Knoxville....then table2 has cities, Luxemburg, Mason, Indine, Freemont

    And let's assume there is only one record for each city in tableone I would want my query results to show

    CITY -----------------Count
    Luxemburg 0
    Mason 1
    Indine 1
    Freemont 1

    Does this help clarify? Sorry for me doing such a poor job explaining prior.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, darn it all to heck

    your left table is table2 !!!

    please excuse the last few posts, i got confused

    the query you want is in post #6

    the left table is table2, and the right table is table1

    COUNT(tbl_one.city) will be 0 automatically when the city does not exist in table1

    (it's too bad you had to use fake names like table1 and table2, that often leads to confusion)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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