How to compare string in left join 2 times?

Question

SQL Server

Hello.
There are 2 tables, in names names, in match - strings for comparing names. You need to append match to names with the condition that the name will contain words from the set with priority 1 AND words from the set with priority 2.
For example, name = 'John Smith Good Guy'
Table match:
compare | priority
---------------------
good | 1
guy | 2
John | 1
Smith | 3
As a result, we should get
John Smith Good Guy | good john smith good
guy | guy john smith good guy
| John
I tried a construct like this but I get compare=null

select n.name, m.compare from names n
 left join match m
  on  (n.name like '%' + m.compare+ '%' and m.priority = 1)
   and 
   (n.name like '%' + m.compare + '%' and m.priority = 2)

Can anyone help?

Answer 1

You have "m.priority = 1" and "m.priority = 2"
(n.name like '%' + m.compare+ '%' and m.priority = 1)
and <--
(n .name like '%' + m.compare + '%' and m.priority = 2)
Use union