Get duplicate records that match 2 IDs but include all from 3rd ID

user_9540872 0
Viewed 76

Hi I have an MS Access/SQL query question. I have a table

ID1     ID2      ID3
 1       1        4
 2       2        5
 1       1        1
 3       1        2
 1       1        3

I want these results:

ID1    ID2       ID3
 1      1         4
 1      1         1
 1      1         3

I want all the records that duplicates when ID3 = 4 but I want the other ID3 values to show. Any records from ID3 = 4 that matches records from the other ID3.

I tried looking up other examples like retrieving states that share the same city names. But I have multiple matches I need, so it would be like retrieving states that share the same count and city names. Any suggestions? I can find the duplicates but I only want the duplicates where ID3 = 4.

ms-access sql
2 Answers

You need this query:

SELECT id1, id2
FROM tablename
GROUP BY id1, id2
HAVING COUNT(*) > 1 AND SUM(IIF(id3 = 4, 1, 0)) > 0

to identify if id1 and id2 of id3 = 4 have any duplicates, so join it to the table:

SELECT t.* 
FROM tablename AS t
INNER JOIN (
  SELECT id1, id2
  FROM tablename
  GROUP BY id1, id2
  HAVING COUNT(*) > 1 AND SUM(IIF(id3 = 4, 1, 0)) > 0
)  AS tt ON tt.id1 = t.id1 AND tt.id2 = t.id2
user_104988280

You can use exists:

select t.*
from t
where exists (select 1
              from t as t2
              where t2.id1 = t.id1 and
                    t2.id2 = t.id2 and
                    t2.id3 = 4
             );
user_11440350
Related