Want to include all states but only including those where count > 0 1

[laina222]

Hi everyone,

I am working with a query where I am joining two tables. One table has all 51 states and a person's name associated with that state. The second table has two fields - state and count (count is associated with clients within the state, if that is relevant at all). Not every state is included in the second table, specifically those with no clients. I want to join the two tables so I have a list of 51 states, the person's name associated with that state, and then the count (even if it is null). My results would look like this:

AK Bob Smith 45
AL John Doe
AR Kate Jones 10
.....

and so on, for all 51 states. Does anyone know of an easy way to do this?

Thanks!

 

[lespaul]

Maybe something like:

SELECT PersonSTateTable.STATE, PersonName, CountField FROM
PersonStateTable
LEFT JOIN StateCount table on PersonStateTable.State = StateCount.State


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

You need an OUTER join, eg (SQL code):
SELECT A.State, A.person, B.Count
FROM Table1 AS A [LEFT JOIN Table2 AS B ON A.State = B.State
ORDER BY A.State

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[laina222]

Thanks Leslie! That worked out perfectly