CODEWITHSUNDEEP
mysqlsqljoin
Avery235
Avery235

Reputation: 5306

MySQL JOIN if either of the 2 tables meets condition

SELECT COALESCE(table2.value, table3.value) FROM table1
JOIN table2 ON (table1.value = table2.value)
JOIN table3 ON (table1.value = table3.value)

table1.value = table2.value and table1.value = table3.value are mutually exclusive (at most one of them will be true).

How do I make the join happen only if either table1.value = table2.value or table1.value = table3.value is true?

Is this the only solution? (which is rather tedious)

SELECT table.value FROM table1
JOIN 
(SELECT COALESCE(table2.value, table3.value) FROM table1
 LEFT JOIN table2 ON (table1.value = table2.value)
 LEFT JOIN table3 ON (table1.value = table3.value)
) as table ON (table1.value = table.value)

Upvotes: 2

Views: 2169

Answers (2)

Jeffrey Arzadon Cabang
Jeffrey Arzadon Cabang

Reputation: 105

try to use inner join and union two tables

SELECT * FROM
(SELECT value, data FROM table1) as a
INNER JOIN
(SELECT value, data FROM table2) as b
ON b.value = a.value
UNION ALL
(SELECT value, data FROM table1) as a
INNER JOIN
(SELECT value, data FROM table3) as b
ON b.value = a.value

Inner JOIN will avoid to join two table that was NULL it will only join those rows who have exact find on both table. in the UNION we just assume that in the first JOIN the other table3 will not be included there so have it as a separate join and UNION it or merge it to the first one so you can have all the data on one query/table as per just keep in mind upon using the UNION all result should have the exact name or field that you wanted to fetch.

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30809

You can use two queries and UNION them, e.g.:

SELECT COALESCE(table1.value, table2.value) FROM table1
JOIN table2 ON (table1.value = table2.value)

UNION

SELECT COALESCE(table1.value, table3.value) FROM table1
JOIN table3 ON (table1.value = table3.value)

Upvotes: 5

Related Questions