Join query excluding results present in secondary table

[Andres_Vaquero]

Hi there, I’m looking for help with the following database query, let me explain:

I have two tables, the first one (‘fields’) contains information about form fields, and the second one (‘form_field’) is a simple table that holds information about which field belongs to which form.
What I am trying to achieve is that given a form id I select all fields from the first table, which do not belong to that form, that last bit being specified in the secondary table. I have worked out this query, which does not do what I want but I believe it’s fairly close:

SELECT f.*, ff.* FROM  field f LEFT JOIN form_field ff ON f.id=ff.field_id 
WHERE ff.form_id!={$formId}

I think the join part of the query should be conditional somehow and I thought LEFT JOIN would do the trick. But if the non-required join does actually happen then check that there is not already an existing field for that form id and if there is then do not select the field from the first table.

Thank you very much for your help!

[DarthGuido]

SELECT f.*, ff.* 
FROM  field f 
LEFT JOIN form_field ff 
ON f.id=ff.field_id 
AND ff.form_id = {$formId}
WHERE ff.field_id IS NULL

[DaveMaxwell]

Since you never want to see matches, you could use NOT EXISTS as well. It performs as well as the outer join and is a little easier to read.

I typically only use the outer join syntax when I want matches AND non-matches.

SELECT f.*
  FROM field f
 WHERE NOT EXISTS (SELECT ff.field_id
                     FROM form_field ff
                    WHERE ff.field_id = f.id
                      AND ff.form_id = {$formId})