ssirowy ssirowy - 5 months ago 19
SQL Question

MySQL left join with default values

I have a couple of tables, one with source data which I'll call

SourceData
and another which defines overridden values for a given user if they exist called
OverriddenSourceData
.

The basic table format looks something this like:

SourceData
| source_id | payload |
--------------------------------
| 1 | 'some json' |
| 2 | 'some more json' |
--------------------------------


OverriddenSourceData
| id | source_id | user_id | overrides
| 1 | 2 | 4 | 'a change' |
------------------------------------------


For a given user, I'd like to return all the Source data rows with the
overrides
column included. If the user has overridden the source then the column is populated, else it is null.

I started by executing a left join and then including a condition for checking the user like so:

SELECT A.source_id, A.payload, B.overrides from SourceData A
LEFT JOIN OverriddenSourceData B
ON A.source_id = B.source_id
WHERE user_id = 4


but then source rows that weren't overridden wouldn't be included ( it was acting like an inner join) (e.g source id 1)

I then relaxed the query and used a strict left join on
source_id
.

SELECT A.source_id, A.payload, B.overrides from SourceData A
LEFT JOIN OverriddenSourceData B
ON A.source_id = B.source_id
# WHERE user_id = 4


This can return more data than I need though (e.g other users who have overridden the same source data) and then I have to filter programatically.

It seems like I should be able to craft a query that does this all the DB level and gives me what I need. Any help?

Answer

You should add your condition on LEFT JOIN clause, if you use WHERE, mysql will do it with INNER JOIN, so try this;)

SELECT A.source_id, A.payload, B.overrides from SourceData A
LEFT JOIN OverriddenSourceData B
ON A.source_id = B.source_id
AND B.user_id = 4
Comments