T-SQL Table Joins - Unique Situation
- by Dimitri
Hello Everyone. This is my first time encountering the case like this and don't quite know how to handle.
Situation: I have one table tblSettingsDefinition, with fields: ID, GroupID, Name, typeID, DefaultValue. Then I have tblSettingtypes with fields TypeID, Name. And I have final table, tblUserSettings with fields SettingID, SettingDefinitionID, UserID, Value. The whole point of this is to have customizable settings. Setting can be defined for a Group or as global setting (if GroupID is NULL). It will have a default value, but if user modifies the setting, an entry is added to tblUserSettings that stores new value. I want to have a query that grabs user settings by first looking at the tblUserSettings, and if it has records for the given user, grabs them, if not retrieves default settings. But the trick is that no matter if user has settings or not, I need to have fields from other two table retrieved to know the setting's Type, Name etc... (which are stored in those other tables).
I'm writing query something like this:
SELECT *
FROM tblSettingDefinition SD
LEFT JOIN tblUserSettings US
ON SD.SettingID = US.SettingDefinitionID
JOIN tblSettingTypes ST
ON SD.TypeID=ST.ID
WHERE
US.UserID=@UserID OR
((SD.GroupID IS NULL)
OR (SD.GroupID=(SELECT GroupID FROM tblUser WHERE ID=@UserID)))
but it retrieves settings for all users from tblUserSettings instead of just ones that match current @UserID. And if @UserID has no records in tblUserSettings, still, all user settings are retrieved instead of the defaults from tblSettingDefinition.
Hope I made myself clear. Any help would be highly appreciated.
Thank you.
