BCLtd BCLtd - 4 months ago 15
SQL Question

MySQL - Joins - Show Data When Doesn't Exist in Other Tables

I've got my join working like so:

SELECT *
FROM fixtures f,
channel c,
fixture_channel_join fcj
WHERE f.matchday = '1'
AND f.fixtures_id = fcj.fixture_id
AND fcj.channel_id = c.id


However, this only shows rows that has data in the channel and
fixture_channel_join
. Is it possible to display all data from fixtures, and if there are no joins just display null data in them columns?

Thanks

Answer

What you did there is an implicit join, which is an INNER JOIN. It's hard to read and a bit confusing, I advise against it.

You're looking for LEFT JOIN or RIGHT JOIN. The LEFT JOIN gives you all values in the left side, filling up with NULL in the right part of the result:

SELECT *
FROM fixtures f,
LEFT JOIN fixture_channel fcj ON f.fixtures_id = fcj.fixture_id
LEFT JOIN channel c ON fcj.channel_id = c.id
WHERE f.matchday = '1'

This will give you all the values in the fixtures table (including those which don't match), followed by the matching values in the fixture_channel table (with NULL values where no matches exist), followed by the matching values in the channel table (with NULL values where no matches exist).

Alternately, a RIGHT JOIN will give you all values from the right side, filling with NULL in the left:

SELECT *
FROM fixtures f,
RIGHT JOIN fixture_channel fcj ON f.fixtures_id = fcj.fixture_id
RIGHT JOIN channel c ON fcj.channel_id = c.id
WHERE f.matchday = '1'

This will give you all values in the channel table (including those which do not match), preceded by the values in the fixture_channel table (with NULL values for those who do not match any channels), preceded by the values in the fixture table (with NULL values for fixture_channel values that don't match fixtures). Went backwards on this to illustrate it's going right-to-left.

More about joins here, with illustrations :)

Comments