Question 246716 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to find duplicate items in a table of a database

Suppose, we’ve a table named college.
In this database 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

Tags:

3

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search