CODEWITHSUNDEEP
sql-serverselect
Ashkan Mobayen Khiabani
Ashkan Mobayen Khiabani

Reputation: 34180

sql server: select values from two different tables

I have two tables PublicSettings and PrivateSettings. PublicSettings has all the default settings for different types of users while PrivateSettings keeps only a user specific setting which its value is different from default settings. I want to read all settings for a user but if the setting exists in private one get the value from this table and if not from the public one.

select settingname, value from PublicSettings where usertype=@type

this select all default settings but for example imagine that the setting "show_captcha_on_login" is disabled by default but same settingname is true for a single user in private settings, I want to get all from public but this one (that exists in private settings) from the private settings.

by the way PrivateSetting has the same column names except it has a user_id column too, for selecting user specific settings

Upvotes: 0

Views: 389

Answers (2)

Mate
Mate

Reputation: 5284

Try this :

 SELECT  
    pub.name, 
    CASE WHEN (pub.name = priv.name ) THEN priv.value ELSE pub.value END AS value
FROM 
    PublicSettings pub 
    LEFT JOIN PrivateSettings priv ON (pub.utype = priv.utype)
WHERE 
    priv.[uid]=1

Upvotes: 1

peterk411
peterk411

Reputation: 236

You must make use of an outer join because the user-specific value only exists for some of the public values. The non-appearance of a private value will return a NULL, which you can use to determine which value to return.

SELECT pub.settingname, 
       ISNULL(priv.value,pub.value) AS value
  FROM PublicSettings  pub 
  LEFT OUTER JOIN PrivateSettings priv 
    ON pub.settingname = priv.settingname
   AND priv.userid = @userid
 WHERE pub.usertype=@type;

Upvotes: 1

Related Questions