Search code examples
sqlinner-joinsql-in

How to get a row from a look up table column contains any

I have a Customer table with the following rows in it:

enter image description here

I have a Lookup table called Master as below:

enter image description here

As you can see in the Master look up I have first name shown with a middle name. What is best SQL quesry to give me the following result:

enter image description here

So basically I want to look up customer's firstname in the master list's firstname column but with any combination. So far I've tried the following but not give me what I need for all

Option 1 - this is not give me distinct ref number

select distinct *.C, msurname.Ref
from Customer c 
inner join Master mfirstname on (c.firstname like '%' + mfirstname.firstname +'%')
inner join Master msurname on (c.surname like '%' + msurname.surname +'%')

Option 2 - this option does not give me the ref number

select c.*
from Customer c
where c.surname in ( select m.surname from master m 
                     where m.surname like '%' + c.surname + '%'
                     and  m.firstname like '%' + c.firstname + '%')

option 3 - could be an option but return dup rows, so need to further check

  select c.*,m.ref
  from Customer c 
  inner join Master m on ((c.firstname like '%' + m.firstname + '%') and  (c.surname like '%' + m.surname + '%'))

What am I doing wrong?

Solution

  • INSTR can come handy

    SELECT Customer.firstname, Customer.lastname, Customer.id, Master.ref
  FROM Customer INNER JOIN Master
ON INSTR(Master.firstname, Customer.firstname) > 0;