0

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
puks1978
  • 3,517
  • 9
  • 40
  • 90

1 Answers1

1

To get the customValue instead of the defaultValue if it is present you can use the coalesce function and to filter by userid you'll want to add the condition to the join instead of using it in a where clause (which would make the left join behave like an inner join).

I'm guessing you want something like this:

select 
    us.userID, 
    s.settingID, 
    us.userSettingID,
    s.name, 
    s.defaultValue,
    us.customValue,
    coalesce(us.customValue, s.defaultvalue) as OverriddenValue
from settings s
left join Usersetting us on s.settingID = us.settingID 
left join user u on u.userid = us.userID and u.userID = 1

user is a non-reserved keyword in MySQL and as such isn't a good choice for an identifier, although it is permitted to be used without quoting.

jpw
  • 42,772
  • 6
  • 57
  • 78
  • Thanks for this. It seems to work OK however if there is another user (say user 2) that has a setting that user 1 does not, user 1 inherits users 2 custom setting instead of using the default. – puks1978 Oct 29 '15 at 23:15
  • @puks1978 Oh right. I misunderstood. Try this: `select *, coalesce(us.customValue, c.defaultvalue) from (select * from users, settings) c left join Usersetting us on us.settingID = c.settingID and us.userID = c.userid where c.userid = 1` – jpw Oct 29 '15 at 23:22
  • PERFECT! Thanks so much. – puks1978 Oct 29 '15 at 23:28