I need to find matchs between two tables, but also need to display when there is no match.

Table1: id, dni_number, name, business_id

Table2: id, dni, business_id

I need to form a table like this:

is_match meaning 1: it found the dni in table1 and also in table2, 0 for not match

The query should have a where condition to find matchs from certain business_id

Any help will be much appreciated. Thanks in advance

CodePudding user response：

SELECT
tblA.id,
1 as is_match
FROM tblA, tblB
WHERE tblA.id = tblB.id

UNION ALL

SELECT
tblA.id,
0 as is_match
FROM tblA, tblB
WHERE tblA.id != tblB.id

CodePudding user response：

SELECT *, (table2.dnu = table1.dnu) AS is_match FROM table1 LEFT JOIN table2 ON table1.business_id = table2.business_id WHERE table1.business_id = xxx;