lundi 13 juin 2016

Unique pairs for sql table

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