How to find duplicate items in a table of a database

Question

Suppose, we've a table named college. In this table we've the following columns: College ID, College name, State and Established in. Now I want to find the names only of those colleges where the college have another branch in the same state.

Recently I've started learning database and couldn't find the solution of this problem anywhere. Please help me write the query in MySQL with explanation

I tried joining the same tables with alias as c1 and c2 and ran some query having select clause inside another select clause but it didn't happen to work

Answer 1

For example, colleges 'C1' & 'C3' are available at two places in states 'S1' & 'S3' respectively.

DECLARE @College TABLE (Id int identity, Name Varchar(50), State 
Varchar(50), Est datetime);

Insert into @College (Name, State, Est)
Values ('C1', 'S1', '01/01/2010'),('C1', 'S1', '01/01/2020'),
('C2', 'S2', '01/01/2010'),
('C3', 'S3', '01/01/2015'),('C3', 'S3', '01/01/2022')

Select Name From @College
Group by Name, State Having Count(*)>1

The above query will return college names 'C1' and 'C3' as below.

Name
C1
C3

Answer 2

I assume you forget to avoid self-matching in join. Also, use distinct college names as per requirement. Try something like this.

SELECT DISTINCT col1.College_Name
FROM College col1
JOIN College col2 ON col1.State = col2.State 
               AND col1.College_ID <> col2.College_ID;

Answer 3

You should have shown your query. A self join as you've mentioned should work.

You want the same college name and the same state. Hence:

select *
from college c1
join college c2 on c2.college_name = c1.college_name
               and c2.state        = c1.state
               and c2.id           > c1.id;

By using c2.id > c1.id we make sure to get each pair just once. So with three rows for 'Super College' in 'New York' with IDs 1, 2, 3, you'd get 1/2, 1/3, 2/3.

An alternative is aggreation. Group your rows such as to get one result row per college name and state and then list the IDs:

select college_name, state, group_concat(id) as ids
from college
group by college_name, state
having count(*) > 1
order by college_name, state;

This would get you this row: Super College | New York | 1,2,3

Answer 4

If you're using mysql version >= 8, you can use a window function.

with q as (
select
    college_name,
    state,
    count(college_id) over (partition by state) as dups
from college
)

select * from q
where dups > 1