mysql查询逻辑，用于在特定条件下从3个相关表中获取数据

Question

I'm building a programming contest controller on web platform, which has some tables, including 'contest', 'problem' and 'relation' tables, which I'm having the trouble with.

structure of 'contest' table: contest_id, contest_name (for simplicity)

============================
|contest_id | contest_name |
|-----------|--------------|
|          1|  Test Contest|
|-----------|--------------|
|          2|   Another One|
============================

structure of 'problem' table: problem_id, problem_name (for simplicity)

============================
|problem_id | problem_name |
|-----------|--------------|
|          1|     A Problem|
|-----------|--------------|
|          2| Other Problem|
============================

structure of 'relation' table: rel_id, contest_id, problem_id

===========================================
|   rel_id  | contest_id   |  problem_id  |
|-----------|--------------|--------------|
|          1|             1|             1|
|-----------|--------------|--------------|
|          2|             1|             2|
|-----------|--------------|--------------|
|          3|             1|             8|
|-----------|--------------|--------------|
|          4|             2|             5|
|-----------|--------------|--------------|
|          5|             2|             8|
===========================================

I'm planning to allow the admin to set up the system once then have as many contests as s/he wants, so the same 'problem_id' can be assigned to multiple 'contest_id'.

For a single contest, I'm fetching all the 'problem_id's for that contest with all the content of that problem with this query:

SELECT * FROM `problem` JOIN `relation` on `relation`.`problem_id` = `problem`.`problem_id` WHERE `contest_id` = 3 // say the id is 3

But when editing the contest and adding some more problems in it, I need to fetch ONLY those problems to show which are NOT ALREADY in the same contest.

I tried this but didn't work, gave me some duplicates and other contest problems:

SELECT * FROM `problem` LEFT JOIN `relation` on `relation`.`problem_id` != `problem`.`problem_id` WHERE `contest_id` != 3

I can do the same thing inside php, using two loops, one for iterating through all the 'problem_id's in the whole system and inside that loop, another loop for iterating through all the 'problem_id's of that contest only, or vice versa. But it'll cost me an O(n^2) complexity which I'm sure can be avoided using mysql query. Any idea to do it in php more efficiently will be also good for me. Any help is appreciated.

</div>

Answer 1

You can use MYSQL NOT IN() to remove the problems that are already in the relation table for the selected contest.

SELECT * FROM problem WHERE 
    problem.problem_id NOT IN (SELECT problem_id FROM relation WHERE contest_id = 2)

NOT IN() makes sure that the expression proceeded does not have any of the values present in the arguments.