MySQL - display rows of names and addresses grouped by name, where name occures more than once
Posted
by Stoob
on Stack Overflow
See other posts from Stack Overflow
or by Stoob
Published on 2009-10-22T00:18:10Z
Indexed on
2010/05/21
16:30 UTC
Read the original article
Hit count: 321
I have two tables, "name" and "address". I would like to list the last_name
and joined address.street_address
of all last_name
in table "name" that occur more than once in table "name".
The two tables are joined on the column "name_id".
The desired output would appear like so:
213 | smith | 123 bluebird |
14 | smith | 456 first ave |
718 | smith | 12 san antonia st. |
244 | jones | 78 third ave # 45 |
98 | jones | 18177 toronto place |
Note that if the last_name "abernathy" appears only once in table "name", then "abernathy" should not be included in the result.
This is what I came up with so far:
SELECT name.name_id, name.last_name, address.street_address, count(*)
FROM `name`
JOIN `address` ON name.name_id = address.name_id
GROUP BY `last_name`
HAVING count(*) > 1
However, this produces only one row per last name. I'd like all the last names listed. I know I am missing something simple. Any help is appreciated, thanks!
© Stack Overflow or respective owner