CODEWITHSUNDEEP
sqlsql-servert-sql
Burak
Burak

Reputation: 507

How to compare rows as lists in SQL?

I have a many to many table which has 3 primary keys shown below :

table1 : key_1, key_2, key_3

I want to compare rows as lists,

For Example :

table1 would be :

      key_1  key_2  key_3

row1:  10  |  100  | 150
row2:  10  |  101  | 150
row3:  10  |  103  | 151

row4:  11  |  100  | 150
row5:  11  |  101  | 150
row6:  11  |  103  | 151

So what I'd like to achive is to compare my table filtered by key_1 and find duplicate list of rows.

So in this scenerio,

SELECT * FROM table1 where key_1 = 10;

returns 3 rows (row1, row2, row3) and

SELECT * FROM table1 where key_1 = 11;

also returns 3 rows (row4, row5, row6)

And as you see above, first result of 3 rows has same key_2 & key_3 values with the second result of 3 rows.

So how can I query this, get rows as lists and compare them?

I know that this question looks something stupid but please, I'd very glad if you help me. Thanks in advance :)

Upvotes: 0

Views: 334

Answers (2)

The Impaler
The Impaler

Reputation: 48800

You can search for unmatched rows of a full outer join.

For example the following query finds any difference between group 10 and 11:

select *
from table1 a
full join table1 b on a.key2 = b.key2 and a.key3 = b.key3
where a.key2 is null or b.key2 is null
  and a.key1 = 10 and b.key1 = 11

If the query returns no rows, then the groups are identical.

Upvotes: 2

jakubiszon
jakubiszon

Reputation: 3573

If you wanted to find key_2 and key_3 pairs which repeat for the two values of key_1:

select key_2, key_3
from table1
where key_1 in (10, 11)
group by key_2, key_3
having count(1) > 1

To find the values of key_1 for which same key_2 and key_3 exist:

select distinct key_1
from table1
inner join (
  select key_2, key_3
  from table1
  group by key_2, key_3
  having count(1) > 1
) t (key_2, key_3)
on table1.key_2 = t.key_2
and table1.key_3 = t.key_3

The above query only tells key_1 value has at least one other key which has the same key_2 and key_3. It does not tell you what is the other key_1 value or what are the key_2 and key3. It seems to be what you were asking for but I am not sure if that is very useful.

Upvotes: 1

Related Questions