How to choose only those who share surnames? SQL
I am still quite new to working with databases and this problem has puzzled me a lot.
I have a People table with first and last names. I am trying to create a query that will only select those who have shared last names with others in a 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
As a result, I want:
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 common names, but only outputs one of them, not all the first names.
I'm sure there is a simple fix for this, but I can't figure it out.
+3
nymkalahi
source
to share
2 answers
You're almost there. Now that you have a list of the most recent names that you care about, just add another query:
select * from people
where last_name in
(
select last_name
from people
group by last_name
having count(*) > 1
)
+3
Joe enos
source
to share
You can use a subquery to do this:
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
+2
Barranka
source
to share