I have 3 tables which store user, available settings and user settings. User table has standard user stuff like username, password, name, date etc. Settings table has a list of all available system settings and a default value like:
settingID name defaultValue
----------------------------------------------
1 push notifications 1
2 profile image 0
3 full name 1
4 distance measure Miles
User setting table has any custom settings set by the user like so:
userSettingID userID settingID customValue
----------------------------------------------------------
1 1 1 0
2 1 3 0
3 1 4 KM
I am trying to make a query that will join all 3 tables together however produce ALL results from the settings table and if there is a match in the userSettings table use the customValue otherwise use the defaultValue from the settings table.
So I would like to produce the below:
userID settingID userSettingID name defaultValue customValue
---------------------------------------------------------------------------
1 1 1 push... 1 0
null 2 null profi.. 0 null
1 3 2 full... 1 0
1 4 3 dista.. Miles KM
As you can see I would like EVERY setting record and if there are matches populate with userID and/or customValue otherwise fill with NULL.
I was able to get the desired results but as soon as I add a WHERE userID = x clause I only get records that a user has been set to.
Thanks
Edit: The query I have so far is:
SELECT *
FROM settings
LEFT OUTER JOIN userSettings ON settings.settingID = userSettings.settingID
LEFT OUTER JOIN user ON userSettings.userID = user.userID