mercredi 22 juin 2016

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, but then source rows that weren't overridden wouldn't be included ( it was acting like an inner join)

I then just used a strict left join on source_id. This will return more data than I need though (e.g other users who have overridden the 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?

Aucun commentaire:

Enregistrer un commentaire