SQL: Filter rows who have same foreign IDs

Question

I have the following table, and I want to only get the rows which have the same foreign keys.

    id  foreign_id
1   100  a
2   200  a
3   300  b
4   400  c
5   500  c
6   600  d

to this:

    id  foreign_id
1   100  a
2   200  a
4   400  c
5   500  c

Does this answer your question? [Find duplicate records in MySQL](https://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql) — P.Salmon, Apr 08 '22 at 06:36
I removed the conflicting DBMS tags. Please add only one tag for the database product you are really using. — , Apr 08 '22 at 06:40

score 0 · Answer 1 · answered Apr 08 '22 at 06:40

You can use a sub-query to count the number of id's per foreign_id and then only return the rows with a foreign_id having 2 or more (>1) id's.

Select
 t.id,
 t.foreign_id
From tablename t
Join
( Select foreign_id fid,
  Count(id) c
  From tablename
  Group by foreign_id ) f
On t.foreign_id = f.fid
Where c > 1;

score 0 · Answer 2 · edited Apr 08 '22 at 07:54

0

SELECT  ID, foreign_id
from tablename
WHERE foreign_id in (SELECT foreign_id
                     FROM tablename
                     group by foreign_id
                     having count(foreign_id) > 1)

edited Apr 08 '22 at 07:54

jarlh

42,561
8
45
63

answered Apr 08 '22 at 06:48

kear

1
2

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 08 '22 at 07:11

SQL: Filter rows who have same foreign IDs

2 Answers2