We help IT Professionals succeed at work.

Matching fields in two tables

I have two tables that i'm trying to match a person to.  Both tables have NPI which I can link to, but if the NPI is missing out of one or both of the tables, then I want it to search for other fields to match.   I want it to then look at last name, then first name, then address

so it would be tblA and tblB

if NPI = NPI else Last Name = Last Name else Address = Address

How would I go about writing this code?
Comment
Watch Question

maybe something like this?
select a.*, b.*
from table1 as A, table2 as B
where 	(a.NPI = b.NPI) or 
	(a.LastName = b.LastName) or 
	(a.Address = b.Addres)

Open in new window

Hamed NasrRetired IT Professional

Commented:
Try ralmada's comment http:#37239304. It looks ok. Give your feedback.

Commented:
Hi,

you can try it with UNION ALL.

SELECT A.*, B.*
FROM tblA A INNER JOIN
      tblB B ON A.NPI = B.NPI
UNION ALL
SELECT A.*, B.*
FROM tblA A INNER JOIN
      tblB B ON A.LastName = B.LastName
UNION ALL
SELECT A.*, B.*
FROM tblA A INNER JOIN
      tblB B ON A.Address = B.Address

Open in new window