Join based on different parameters when no matching identifier-CodePudding

Table 1

table1_id
employee_id
address
...

Table 2

table2_id
employee_id
address
...

Query:

SELECT * 
FROM Table 1 AS A
INNER JOIN TABLE 2 AS B A.employee_id = B.employee_id

There can be instances where table A and table B can't be joined on employee_id due to input errors, in this case I can want to join on address. I'm not sure how to include this logic. I was thinking about a case statement that joins can join based on if employee_id does not match, however, the query would then try to match with every other row in the table. Not sure if I am on the right track.

CodePudding user response：

Not sure if I understand correctly, but are you wanting this?

SELECT * FROM Table 1 AS A
INNER JOIN TABLE 2 AS B
WHERE (A.employee_id = B.employee_id) OR ((A.employee_id != B.employee_id) AND (A.employee_addr = B.employee_addr))

That would be match where the employee_id is the same, or if employee_id is not a match then match on employee address.

CodePudding user response：

I would suggest two left joins:

SELECT a.*, COALESCE(b_e.col1, b_a.col1) as col1, . . . 
FROM Table1 a LEFT JOIN
     Table2 b_e
     ON A.employee_id = b_e.employee_id LEFT JOIN
     Table2 b_a
     ON A.adress = b_a.address AND b_e.employee_id IS NULL
WHERE b_e.employee_id IS NOT NULL OR b_a.address IS NOT NULL;

The WHERE clause turns the outer joins back into an "inner" join, by requiring one match to the join conditions.