Clever Geek Handbook

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


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


source


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


source






More articles:


All Articles