Multiple JOINS do not work in a nested JOIN-CodePudding

Why am I unable to join multiple tables?

If I were to run this SQL code without the second INNER JOIN, it works just fine.

SELECT a.name, c1.text, c2.text
FROM `table_a` a
INNER JOIN (
    `table_b` b
    INNER JOIN `table_c1` c1 ON b.info_id = c1.id
    INNER JOIN `table_c2` c2 ON b.info_id = c2.id
) ON a.id = b.person_id;

However, if I add a second JOIN -> INNER JOIN table_c2 c2 ON b.info = c2.id, it ceases to work properly, and won't select anything.

Here is a I would want there to be a third row with name "Bob", and text "more_other_table_text"

CodePudding user response：

There are a few things you can do to achieve this:

LEFT JOIN between table_b and c1 & c2.
IFNULL to check for text in c1, otherwise return text from c2.
GROUP BY without an aggregate function to remove the duplicate Sam values from the result set based on the order they were entered into table_2 (you did not specify what value you want when the same person_id is entered multiple times in table_2, if you would like to see both c1 and c2 texts with duplicate names, remove the GROUP BY).

See Fiddle

SELECT a.name, IFNULL(c1.text, c2.text) as `text`
    FROM `table_a` a
    INNER JOIN (
        `table_b` b
        LEFT JOIN `table_c1` c1 ON b.info_id = c1.id
        LEFT JOIN `table_c2` c2 ON b.info_id = c2.id
    ) ON a.id = b.person_id 
     GROUP BY a.name
     ORDER BY a.id ASC

CodePudding user response：

The simplest solution is to put your join condition next to the join:

SELECT a.name, b.age, c1.text, c2.text
 FROM table_a a
 INNER JOIN table_b b ON a.id = b.person_id
 INNER JOIN table_c1 c1 ON b.info = c1.id
 INNER JOIN table_c2 c2 ON b.info = c2.id

You may also use parentheses, but there is no advantage to doing so:

SELECT a.name, b.age, c1.text, c2.text
 FROM table_a a
 INNER JOIN (
    table_b b
    INNER JOIN table_c1 c1 ON b.info = c1.id
    INNER JOIN table_c2 c2 ON b.info = c2.id
 )
 ON a.id = b.person_id