Menu
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!
336
When I use the left join with distinct, it applies in the fact table and not in the category inline load. It turns out it perfoms the join operation first and then the distinct.
In simple words, we can say that SELECT UNIQUE statement is used to retrieve a unique or distinct element from the table. Let's see the syntax of select unique statement. SQL SELECT DISTINCT statement can also be used for the same cause.
Change != to < in your WHERE clause to ensure that the lowest ID always comes first:
WHERE b1.Id < b2.Id If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
