Multiple JOINS do not work in a nested JOIN

Question

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 sqlfiddle for the following problem.

My desired result is that I want it to select from both tables table_c1 and table_c2.

Here it selects only with table_c1.

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

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

Answer 1

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

1. LEFT JOIN between table_b and c1 & c2.
2. IFNULL to check for text in c1, otherwise return text from c2.
3. 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