Select 2 rows from table 1 (with UNION) if the pair does not exist in table 2

Question

So, I have two tables:

Uno table:

id | gender | lf ------------------------- abc | 1 | 2 cde | 2 | 1 efg | 1 | 2 

table duet :

 id1 | id2 ------------------------- abc | cde 

My working (for now) query selects exactly 2 rows from the uno table:

 (SELECT * FROM uno WHERE gender = 1 AND lf = 2 ORDER BY RAND() LIMIT 1) UNION (SELECT * FROM uno WHERE gender = 2 AND lf = 1 ORDER BY RAND() LIMIT 1) 

What returns:

 id | gender | lf ------------------------- abc | 1 | 2 cde | 2 | 1 

What I need (and did not find out whether it is feasible with the request) is to return 2 identifiers that are not paired in the duet table.

In this example, the above query should not return abc and cde , because they are already in the duo table (possible pairs are efg and abc, efg and cde, since they are not found in the duo table).

Thanks!

UPDATE: With chiliNUT, I came up with this:

 (SELECT id,gender,lf FROM uno u1 WHERE NOT EXISTS /* id1-centric exclusion rule */ /* look at id1 FROM duo, exclude it IF it IS IN uno AND id2 IS also IN uno */ (SELECT d.id1, d.id2, u2.id FROM duo d LEFT JOIN uno u2 /* note the JOIN order, duo on uno */ ON d.id2 = u2.id WHERE d.id1 = u1.id) /* id2-centric exclusion rule */ /* look at id1 FROM duo, exclude it IF it IS IN uno AND id2 IS also IN uno */ AND NOT EXISTS (SELECT d.id1, d.id2, u2.id FROM uno u2 LEFT JOIN duo d /* note the JOIN order, uno on duo */ ON d.id1 = u2.id WHERE d.id2 = u1.id) and (gender=1 and lf=2)order by rand() limit 1)UNION(SELECT id,gender,lf FROM uno u1 WHERE NOT EXISTS /* id1-centric exclusion rule */ /* look at id1 FROM duo, exclude it IF it IS IN uno AND id2 IS also IN uno */ (SELECT d.id1, d.id2, u2.id FROM duo d LEFT JOIN uno u2 /* note the JOIN order, duo on uno */ ON d.id2 = u2.id WHERE d.id1 = u1.id) /* id2-centric exclusion rule */ /* look at id1 FROM duo, exclude it IF it IS IN uno AND id2 IS also IN uno */ AND NOT EXISTS (SELECT d.id1, d.id2, u2.id FROM uno u2 LEFT JOIN duo d /* note the JOIN order, uno on duo */ ON d.id1 = u2.id WHERE d.id2 = u1.id) and (gender=2 and lf=1) order by rand() limit 1) 

I needed to edit to get 2 identifiers (with different sexes and lf). This is definitely not optimized and will probably hang my DB, but this is the beginning! Thanks chiliNUT!

Answer 1

The following query will select all id in uno , where id not a member of id1,id2 . It looks at each id in uno , then checks to see if it duo.id1 , and if so, it checks to see if the associated duo.id2 in uno . Then, in the other direction, it checks to see if this matches the id in uno a duo.id2 , and then checks if duo.id1 in uno.

 SELECT id FROM uno u1 WHERE NOT EXISTS /* id1-centric exclusion rule */ /* look at id1 FROM duo, exclude it if it is in uno AND id2 is also in uno */ (SELECT d.id1, d.id2, u2.id FROM duo d LEFT JOIN uno u2 /* note the JOIN order, duo on uno */ ON d.id2 = u2.id WHERE d.id1 = u1.id) /* id2-centric exclusion rule */ /* look at id2 FROM duo, exclude it id it is in uno AND id1 is also in uno */ AND NOT EXISTS (SELECT d.id1, d.id2, u2.id FROM uno u2 LEFT JOIN duo d /* note the JOIN order, uno on duo */ ON d.id1 = u2.id WHERE d.id2 = u1.id) |id | +---+ |efg| 

If you delete the clause of the 2nd condition, it returns cde , but not abc . if you delete the 1st existence sentence, it returns abc , but not cde . Therefore, both are necessary.