CODEWITHSUNDEEP
sqlsqlite
Eric
Eric

Reputation: 3363

Select unique pairs in self join

I'm trying to write a simple query in sqlite with a self join. I want all the pairs of IDs of the products that have the same cost, but I want unique pairs (i.e. don't list the same pair twice even in different order). Here's what I've got:

SELECT b1.Id, b2.Id
FROM Basic AS b1
LEFT JOIN Basic AS b2
ON b1.cost = b2.cost
WHERE b1.Id != b2.Id
AND b1.Cost = 5;

So I get something like

23 | 101
23 | 205
24 | 103
101 | 23 <-- Duplicate!

I've tried different combinations of DISTINCT and GROUP BY but I still getting dupicate pairs:

I've tried stuff like

SELECT DISTINCT bp1.Id, bp2.Id ...

& ... = 5 GROUP BY bp1.Id, bp2.Id;

How can I get rid of duplicate pairs? Any ideas?

I will appreciate your help!

Upvotes: 24

Views: 12575

Answers (2)

scottlittle
scottlittle

Reputation: 20822

If you have more than 1 set of IDs with different lengths, for example, a simple comparison of IDs WHERE b1.Id < b2.Id will not work. To get all IDs in the same working space, you could take the MD5 hash in the where clause:

WHERE MD5(b1.Id) < MD5(b2.Id)

In sqlite, the MD5 hash function may not be built-in, but in its place you can use any hash function that may be built-in or create your our user-defined hash function.

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838036

Change != to < in your WHERE clause to ensure that the lowest ID always comes first:

WHERE b1.Id < b2.Id

Upvotes: 45

Related Questions