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
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!