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(
DECLARE @ClientID varchar(10)
SET @ClientID = 'Client1'
SELECT NULL, '', 20
SELECT NULL, '', 30
SELECT NULL, '', 40
SELECT @ClientID, '', 100
SELECT @ClientID, '', 200
SELECT @ClientID, '', 300
As you can see SettingName  has no default value and SettingName  has no client specific value. But we want to see a list of all the four values. For the settings ,, and  we want to use the client specific data and for the setting  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.
Name = COALESCE(t1.SettingName, t2.SettingName),
Value = CASE
WHEN t1.ClientID IS NOT NULL
(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