JOIN with OR condition and use only MIN(Column)

I have two tables. Certain values from table t need to be matched with certain values of table m in order to identify a target value from table m.

Table t looks as follows.

ID | A | B | C 
1  | x | y | z
2  | 1 | 2 | 3

Table m looks as follows.

matchA | matchB | matchC | priority | targetValue
NULL   | y      | NULL   | 1        | this
NULL   | NULL   | z      | 2        | that

The logic should be as follows: Column A from table t should be matched with column matchA from table m and so on. As you can see in the example, there can be multiple matches. In the first row of table t the value y of column B matches with the y of first row of table m in column matchB. However, the first row of table t also matches with the second row of table m with the z value in columns C and matchC. If this is the case, only the match with the lowest priority should be returned in the resultset. The value of the column targetValue should be shown.

I know how to do the matching, but I don’t know how to filter down to just the result with the lowest priority.

This is what I have so far.

SELECT * FROM t
    INNER JOIN m
        ON m.matchA = t.A
        OR m.matchB = t.B
        OR m.matchC = t.C

This returns both matching rows. However, I only want the match with the lowest value in the column priority.

Answer

This looks like a good use-case for a lateral join — or APPLY in the language of SQL Server:

SELECT t.*, m.*
FROM t OUTER APPLY
     (SELECT TOP (1) m.*
      FROM m
      WHERE m.matchA = t.A OR
            m.matchB = t.B OR
            m.matchC = t.C
      ORDER BY m.Priority
     ) m