SQL: Filter rows who have same foreign IDs-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

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