Mysql – Subquery or multiple left joins, or both

join;MySQLsubquery

I have two tables, for simplicity table1, with id as Primary key, and a field name, and table2 with the same. Each table has quite a few records, but during a complicated form process, the user is presented with every record from table1, but depending on their choice in the form (done by radio, with id as the value) they may not have every result from table2 listed on the next part of the form. This is based on a 3rd table, table3, with id as primary key, table1_id, table2_id as records, which lists "invalid combinations" from table1 / table2.

So, I'm trying to generate the sql for showing table2, which needs to NOT have any records that match the choice from table1, that are listed in table3.

Subquery Table3, with a where statement of the choice made on table1, and use that as a NOT NULL Left join on table2? Every time I try to write the SQL I mess something up.

Best Answer

There are many ways to do this. Most common are with a LEFT JOIN / IS NULL check, NOT IN or NOT EXISTS subquery. Here's a solution with the 3rd option:

SELECT t2.id, t2.name 
FROM table2 AS t2
WHERE NOT EXISTS
      ( SELECT *
        FROM table3 AS t3
        WHERE t3.table1_id = @t1_id     -- the t1.id value (choice) that you want to check
          AND t3.table2_id = t2.id
      ) ;
Related Question