MySQL select complementary pairs on particular values I have the following table (MySQL): 



I need to select the complementary pairs of accessions having different properties grouped by folder. In other words I do not want to group accessions having same property values for one folder.

As a final result I need to get:



I came up with something like that, but I am getting doubled results (strait and reversed pairs).

select    distinct a.accession, a.folder, b.accession, b.folder
from      main a 
     join main b on a.accession!= b.accession AND a.folder= b.folder
where    a.property not in (select c.property from main c where c.accession = b.accession)
and      b.property not in (select c.property from main c where c.accession = a.accession)
group by a.accession, a.folder, b.accession;
Try changing your JOIN statement as follows:

     join main b on a.accession &lt; b.accession AND a.folder= b.folder


This should include a.accession = "Z1" and b.accession = "Z2", but exclude a.accession = "Z2" and b.accession = "Z1".