Let’s say you have a group of settings in your database. There are some settings that are specific for each client. But those settings can also be generic and be used for all the clients. So if ClientID column contains the key of your client you use that value but if there is no such value specified the value defaults to the value specified in the column with ClientID set to NULL. So in a way your client specific value inherits from the default value and if there is no specific value the default is used.

Now the things get more complicated if you can have some setting with a client specific value and no default value and some with a default value and no client specific value.

Let’s set up a test table and test data first.

DECLARE @Tbl TABLE(
    ClientID varchar(10),
    SettingName varchar(10),
    Value int
)
DECLARE @ClientID varchar(10)
SET @ClientID = 'Client1'


INSERT @Tbl
    SELECT NULL, '[2]', 20
INSERT @Tbl
    SELECT NULL, '[3]', 30
INSERT @Tbl
    SELECT NULL, '[4]', 40

INSERT @Tbl
    SELECT @ClientID, '[1]', 100
INSERT @Tbl
    SELECT @ClientID, '[2]', 200
INSERT @Tbl
    SELECT @ClientID, '[3]', 300

As you can see SettingName [1] has no default value and SettingName [4] has no client specific value. But we want to see a list of all the four values. For the settings [1],[2], and [3] we want to use the client specific data and for the setting [4] we want to default to the one with ClientID = NULL.

Then we can use FULL OUTER JOIN of the two sets to align them by setting name and then use CASE function to get the value with higher precedence.

SELECT
  Name = COALESCE(t1.SettingName, t2.SettingName),
  Value = CASE 
WHEN t1.ClientID IS NOT NULL 
  THEN 
    t1.Value 
  ELSE 
    t2.Value 
  END
FROM 
  (SELECT  * FROM @Tbl WHERE ClientID = @ClientID) as t1
FULL OUTER JOIN 
  (SELECT  * FROM @Tbl WHERE ClientID IS NULL) as t2 
ON t1.SettingName = t2.SettingName