SQL : Multiple left joins with similar tables

Question

I have the following tables:

TABLE A
id
info

TABLE B
f_id
question
choices

TABLE C
f_id
question
lines

The id from the Table A always match a f_id from either Table B or C, but never both. I want to join Table B and Table C on table A only when it matches so I would get a table with the following columns :

id |   info   |   question   | choices | lines

where all rows are filled in the question column, some are NULL in the column choices and some are NULL in the column lines.

What I tried is to do two consecutive left joins, but the second one overrides the first so all the rows that doesn't match in Table C (second left join) get a NULL value in the question column.

Is there a way to do a query that will not override previously joined data with NULL values? I'm working with Laravel Eloquent, so any of raw SQL or Eloquent Query would help me.

Consider doing an inner join between A and the outer join of B and C. — Pablo Oliva, Nov 17 '17 at 16:14
Check this definition for help: https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join?rq=1 — Pablo Oliva, Nov 17 '17 at 16:15
You should be using Relationships... https://laravel.com/docs/5.5/eloquent-relationships — Option, Nov 17 '17 at 16:21
I don't understand why you're after Raw SQL if you're using laravel... — Option, Nov 17 '17 at 16:25
Please include your query. I suspect you just need to be using `COALESCE(table_b.question, table_c.question) AS question` — MatBailie, Nov 17 '17 at 17:16

score 1 · Answer 1 · answered Nov 17 '17 at 16:24

1

but never both

Good luck with that.

id | info | question | choices | lines

SELECT a.id, a.info, b.question, b.choices, '' AS lines
FROM tableA as A
LEFT JOIN tableB AS b
ON a.id=b.f_id
UNION
SELECT a.id, a.info, c.question, '', c.lines
FROM tableA as A
INNER JOIN tableC AS c
ON a.id=c.f_id

answered Nov 17 '17 at 16:24

symcbean

47,736
6
59
94

The first query will always return all rows from table a. The second query will sometimes return rows from table a. This means that sometimes you get duplication. Provided the OP means that the rows in table a Always have one and exactly one matching row in table b or c, you can just change the left join to an inner join. And change that UNION to UNION ALL... – MatBailie Nov 17 '17 at 17:21

score 1 · Accepted Answer · answered Nov 17 '17 at 17:07

UNION B and C and then INNER JOIN A to those results.

SELECT s1.f_id, s1.question, s1.choices, s1.lines
FROM 
(
  SELECT f_id, question, choices, lines = null
  FROM B
  UNION 
  SELECT f_id, question, choices = null, lines
  FROM C
) s1
INNER JOIN A ON s1.f_id = A.id

Peter M · Answer 3 · 2017-11-17T16:31:56.953

You could use UNION to combine two different queries.

SELECT 
    `id`, `info`, `question`, `choices` AS `lines`
FROM
    `TABLE_A`  INNER JOIN
    `TABLE_B` ON `TABLE_A`.`id` = `TABLE_B`.`f_id` 

UNION 

SELECT 
    `id`, `info`, `question`, `lines`
FROM
    `TABLE_A` INNER JOIN
    `TABLE_C` ON `TABLE_A`.`id` = `TABLE_C`.`f_id`

Make sure to use JOIN or INNER JOIN (JOIN defaults to INNER JOIN on MySQL) AND not LEFT JOIN, RIGHT JOIN, OUTER JOIN otherwise you'll end up with partially filled results.

SQL : Multiple left joins with similar tables

3 Answers3