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.

up vote 1 down vote accepted

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
      ) ;
  • I was originally doing left join / IS NULL, but something about the fields I was selecting caused an SQL error. Have not tested this method yet, but I think it's exactly what I need. – Zaehlas Sep 26 '14 at 17:35
  • Thank you. Got it working. Left Join may be faster, but harder to fit in a variable I'm checking. the NOT EXISTS is smoother for what I'm doing. – Zaehlas Sep 26 '14 at 17:47
  • Converting to a left join is easy, just use this in the ON condition: t2 LEFT JOIN t3 ON t3.table1_id = @t1_id AND t3.table2_id = t2.id – ypercubeᵀᴹ Sep 26 '14 at 17:51

Check this post out from a similar request to find records NOT IN another table.

MySQL “NOT IN” query

SELECT * FROM Table2 WHERE Table2.principal NOT IN (SELECT principal FROM table1)

Hope that helps!

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.