MySQL concat question

Question

Hi guys! I am somewhat mystified by concat and joins in MySQL 4. Here's where I am having an issue. I have two tables...

person

id, fname, lname, city, state, zip


capital

state, city

I am needing to generate all states and the number of persons (from the person table) from each. Something like ....

AK | 5

AL | 7

AZ | 0

etc etc All states are listed in the capital table but there may be a state like AZ that has no persons.

Any advice would be appreciated. I so rarely am asked to do anything MySQL related and I'm stumped.

Lucy

Answer 1

SELECT   CONCAT(state, ' | ', CAST(count(*) AS CHAR))
FROM     person 
GROUP BY state

As per the update, to get the states with 0 persons:

Solution 1:

SELECT   CONCAT(state, ' | ', CAST(count(*) AS CHAR))
FROM     person 
GROUP BY state
UNION 
SELECT   CONCAT(state, ' | 0')
FROM     capital
WHERE    NOT EXISTS 
         (SELECT 1 FROM person WHERE capital.state = person.state) 

Solution 2: Use outer join of 2 tables on state, and do group by state on the outer join results.