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.
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
)
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句