If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Left Join

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 126
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?
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 126
The count of id is for table_one.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #5 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,815
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.
Reply With Quote
  #6 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 126
r937 tring that code also does not display a 0 if the count of the city name in table1 is 0
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 126
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.
Reply With Quote
  #9 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 126
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
Reply With Quote
  #11 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 126
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.
Reply With Quote
  #13 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On