MySQL - display rows of names and addresses grouped by name, where name occures more than once

Question

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!

Answer 1

SELECT name.name_id, name.last_name, address.street_address, count(name.last_name) as last_name_count
FROM `name`
JOIN `address` ON name.name_id = address.name_id
HAVING last_name_count > 1

Answer 2

You could use a subquery as below, or use the same idea to join to a derived table if you prefer.

SELECT name.name_id, name.last_name, address.street_address
FROM `name`
JOIN `address` ON name.name_id = address.name_id
WHERE name.name_id IN (
  SELECT GROUP_CONCAT(name.name_id)
  FROM `name`
  GROUP BY `last_name`
  HAVING count(*) > 1
)
ORDER BY `last_name`

Answer 3

Use:

SELECT t.name_id,
       t.last_name,
       a.street_address
  FROM NAME t
  JOIN ADDRESS a ON a.name_id = t.name_id
  JOIN (SELECT n.last_name
          FROM NAME n
      GROUP BY n.last_name
        HAVING COUNT(*) > 1) nn ON nn.last_name = t.last_name