MySQL console question
I'm a bit puzzled about concat and joining MySQL 4. Here's where I have my problem. I have two tables ...
person
id, fname, lname, city, state, zip
capital
state, city
I need to generate all states and number of people (from character table) from each. Sort of....
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 people.
Any advice would be appreciated. I so seldom ask you to do anything MySQL-related, and I'm stumped.
Lucy
+2
a source to share
1 answer
SELECT CONCAT(state, ' | ', CAST(count(*) AS CHAR))
FROM person
GROUP BY state
As per the update, to get 0 people states:
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 an outer join of 2 status tables and group by the outer join result.
+1
a source to share