Multiple JOINS do not work in a nested JOIN

user_7695117 0
Viewed 51

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;

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

mysql sql
2 Answers

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
Bill_Karwin_208600

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
user_30928470
Related