I have the following table:

Filters

And I have another like this:

Forms

Now the idea is to join both tables and if a filter matches replace the name.

For instance the first filter should match the first form and the second filter should match only the second form because the first filter is more specific and matches a form already.

I tried the following:

SELECT coalesce(fi.value, fo.id)
FROM Forms fo
left outer JOIN Filters fi
    ON ((fo.id = fi.filter_id OR fi.filter_id IS NULL) AND
       (fo.name = fi.filter_name OR fi.filter_name IS NULL) AND
       (fo.title = fi.filter_title OR fi.filter_title IS NULL) AND
       (fi.filter_id IS NOT NULL or fi.filter_name is NOT NULL or fi.filter_title is not null));

Unfortunately this will match even if there is a more specific filter defined and will return more rows than expected.

Expected: F1, F2

Got: F1, F2, F2

Fiddle

CodePudding user response：

You can add a specificity column to each filter. Assuming you consider a filter where filter_id is not null more specific as a filter where filter_name is not null, or even filter_name and filter_title aren't null. You can then for each (id, name, title) triplet get the match with the highest specificity:

with cte as
(SELECT *, coalesce(fi.value, fo.id), 
case when filter_id is not null then 4 else 0 end
  case when filter_title is not null then 2 else 0 end
  case when filter_id is not null then 1 else 0 end
as specificity
FROM Forms fo
left outer JOIN Filters fi
    ON ((fo.id = fi.filter_id OR fi.filter_id IS NULL) AND
       (fo.name = fi.filter_name OR fi.filter_name IS NULL) AND
       (fo.title = fi.filter_title OR fi.filter_title IS NULL) AND
       (fi.filter_id IS NOT NULL or fi.filter_name is NOT NULL or fi.filter_title is not null)))
select distinct on(id, name, title) value from cte  
order by id, name, title, specificity desc;

Fiddle

If however you don't consider filter_id more specific, then just change the values 4 and 2 to 1 in the query.