CODEWITHSUNDEEP
mysqlsql
nymkalahi
nymkalahi

Reputation: 33

How do I select only people who share last names? SQL

I'm still pretty new to working with databases, and this problem has me stumped.

I've got a People table with first and last name columns. I'm trying to create a query that will only select those who share last names with others in the table.

For example if I have these two columns:

First_Name      Last_Name
John            Doe
Jane            Doe
Mary            Shmo
Kate            Shmo
Matt            Diego
Joe             Smith

The result I want would be:

First_Name  Last_name
John        Doe
Jane        Doe
Mary        Shmo
Kate        Shmo

The code I have is:

select count(*), last_name, first_name 
from people
group by last_name
having count(*) > 1

This gets the shared last names, but only outputs one of each, instead of all the first names as well.

I'm sure there is a simple fix for this, but I can't figure it out.

Upvotes: 3

Views: 2606

Answers (2)

Joe Enos
Joe Enos

Reputation: 40403

You're almost there. Now that you have the set of last names you care about, just wrap another query around that:

select * from people
where last_name in
(
    select last_name
    from people
    group by last_name
    having count(*) > 1
)

Upvotes: 3

Barranka
Barranka

Reputation: 21047

You can use a subquery to get this done:

select p.*
from people as p
     inner join (
                   select last_name 
                   from people 
                   group by last_name 
                   having count(first_name) > 1
                ) as a on p.last_name = a.last_name

Upvotes: 2

Related Questions