How can I select all the rows in a MySql table which have unique pairs from two columns containing a particular element. In a table with columns A and B, I need all the rows containing x in either of A or B, but with unique (A,B) pairing, order doesn't matter.
For e.g,
-----------------
+ id | A | B
+ 1 | cat | dog
+ 2 | cat | rat
+ 3 | deer | cat
+ 4 | rat | cat
+ 5 | dog | cat
The unique pairs in (A,B) are (cat, dog), (cat, rat), and (deer, cat). So I want the rows (1 or 5), (2 or 4) and 3.
I have tried
select * from table GROUP BY A, B HAVING (A='cat' OR B='cat')
but this gives me rows with both (cat, dog) and (dog, cat). I only need one of those.
How to apply mySql query so as to get only rows with distinct pairs?
Aucun commentaire:
Enregistrer un commentaire