I am running the below syntax in SQL Server 2000
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?

From which table is the [ID] field you are using in the COUNT(ID) ???

The count of id is for table_one.

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.

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

the mistake is that you got your left and right tables mixed up

try this --in a LEFT OUTER JOIN, if you COUNT() a column from the right table, you don't have to check the count

r937 tring that code also does not display a 0 if the count of the city name in table1 is 0

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?

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

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

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.

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)