I have the following table:

Filters

And I have another like this:

Forms

Now the idea is to join both tables by the fields that they match if the filter is not null.

For instance the first filter should match the first form and the second filter should match both forms and the third should match none.

I tried the following:

SELECT COALESCE(filter_1, id), COALESCE(filter_2, name), COALESCE(filter_3, title)
FROM FORM
JOIN FILTER on (id = filter_1 or name = filter_2 or title = filter_3)

Unfortunately this will match if one of the fields match and if I change to and we will match if all fields match.

CodePudding user response：

You may add conditions to the ON clause of the join which match on either a value or match or a NULL:

SELECT fi.*
FROM Filters fi
INNER JOIN Forms fo
    ON fi.filter_1 = fo.id AND
       (fo.name = fi.filter_2 OR fi.filter_2 IS NULL) AND
       (fo.title = fi.filter_3 OR fi.filter_3 IS NULL);

Demo