Mysql query logic to extract data from 3 related tables under certain conditions

Question

I am creating a programming contest controller on a web platform that has several tables, including the contest, problem, and attitude tables that I am having problems with.

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

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

Run codeHide result

table structure "problem": problem_id, problem_name (for simplicity)

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

Run codeHide result

Relations table structure: 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|
===========================================

Run codeHide result

I plan to allow the administrator to configure the system once, after which there will be as many contests as he wants, so several "contest_id" can be assigned to the same "problem_id".

"problem_id" :

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

Hide result

, , , , .

, , :

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

Hide result

php, , "problem_id" , "problem_id" , O (n ^ 2), , , mysql. php . .

Answer 1

MYSQL NOT IN(), , relation contest.

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

NOT IN() , , .