CODEWITHSUNDEEP
phpmysqlsql
matt
matt

Reputation: 787

MySQL Selecting ID's that are not in another table

I'm trying to write a query which selects two ID's from a list of Items, which have never met in a head-to-head match (previous head-to-head matches are stored in a table called Face-offs). The intention is the select two ID's for the next match. They should be random, so you could keep running the query, and keep returning a new random face-off.

Items:

+----+-----------+
| ID | Item name |
+----+-----------+
|  1 | trees     |
|  2 | plants    |
|  3 | animals   |
+----+-----------+

Face-Offs:

+--------+-------+
| winner | loser |
+--------+-------+
|      1 |     2 |
|      2 |     3 |
+--------+-------+

Currently, I have this query:

select id from items order by rand() limit 2

to select two random item IDs, however, I'm not sure how to find out if they have met in two different columns of the Face-Off table before.

Can this query be done with just MySQL or would I have to loop the query over and over until a result is returned?

Upvotes: 1

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You should return a row with two items that have not met the face off. The simple way to write the query is:

select i1.id as id1, i2.id as id2
from items i1 cross join
     items i2 left join
     faceoffs f
     on (f.winner = i1.id and f.loser = i2.id) or
        (f.winner = i2.id and f.loser = i1.id)
where f.winner is null and i1.id <> i2.id
order by rand()
limit 1;

This may work for you. However, the performance may be abyssmal. The following is an approach that might have better performance because it chooses one randome item first. The downside is the random might might have faced off to everything else, so it might not return anything. You can just call it again:

select i1.id
from (select id
      from items
      order by rand()
      limit 1
     ) i1 cross join
     items i2 left join
     faceoffs f
     on (f.winner = i1.id and f.loser = i2.id) or
        (f.winner = i2.id and f.loser = i1.id)
where f.winner is NULL and i1.id <> i2.id;

Upvotes: 1

Related Questions