CODEWITHSUNDEEP
mysqldatabasejoin
ssirowy
ssirowy

Reputation: 75

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?

Upvotes: 0

Views: 893

Answers (1)

Blank
Blank

Reputation: 12378

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

Upvotes: 1

Related Questions