Select 2 rows from table 1(with UNION) if pair doesn't exist in table 2

Question

So i have two tables:

Table uno:

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

table duo:

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

My working (so far) query selects exactly 2 rows from table uno:

(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)

Which returns:

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

What I need (and haven't figured out if is doable with a query) is to return 2 ids which are not paired in table duo.

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

Thanks!

UPDATE: With the help of chiliNUT i came 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 had to edited so it will come up with 2 ids(with different gender and lf). This for sure is not optimized and will probably hang my DB but its a start! Thanks chiliNUT!

Answer 1

The following query will select all ids in uno, wherein the id is not a member of an id1,id2 pair. It looks at each id in uno, then checks if it matches a duo.id1, and if so, it checks if the associated duo.id2 is also in uno. Then, in the other direction, it checks if that id in uno matches a duo.id2, and then checks if the duo.id1 is also 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 remove the 2nd existance clause, it returns cde, but not abc. if you remove the 1st existence clause, it returns abc, but not cde. So both are needed.