CODEWITHSUNDEEP
sqlsql-serverselectjoinsql-server-2012
Divya Rose
Divya Rose

Reputation: 225

If one table doesn't have value get it from another table

I have two properties table.

Table1: App_conf
prop_name
prop_value
prop_desc

Table2: Client_conf
client_id
prop_name
prop_value
prop_desc

I want to write a query where first I need to check in Client_conf table with a particular prop_name and client_id. If there is no matching rows in Client_conf table, then I need to check App_conf table with the prop_name.

E.g., values in App_conf table:

'Max Amount Limit', '2000', 'Maximum amount'
'Min Amount Limit', '200', 'Minimum amount'

Values in Client_conf table:

'1','Max Amount Limit', '1500', 'Maximum amount'

Case 1:

prop_name = 'Max Amount Limit', client_id = '1'

In this case, I need the value 1500 from Client_conf table.

Case 2:

prop_name = 'Min Amount Limit', client_id = '1'

Since this is not present in Client_conf table, I need to get the value from App_conf table and the result will be 200.

I need to do this in a single query with optimum performance.

I am using MS SQL.

Upvotes: 1

Views: 122

Answers (2)

Mureinik
Mureinik

Reputation: 311163

You could left join the client_conf table on the app_conf table so you get nulls where there isn't an appropriate client configuration. The use coalesce to ensure you get some configuration, where the client's conf. is prioritized in case it exists:

SELECT    a.prop_name,
          a.prop_desc
          COALESCE(c.prop_value, a.prop_value) AS prop_value,
FROM      app_conf a
LEFT JOIN client_conf c ON a.prop_name = c.prop_name AND 
          c.client_id = 'some id'
WHERE     a.prop_name = 'some name' AND

Upvotes: 1

William
William

Reputation: 6610

Try this,

SELECT    COALESCE(CC.prop_value, AC.prop_value) AS prop_value,
FROM      app_conf AS AC LEFT OUTER JOIN 
                client_conf AS CC ON AC.prop_name = CC.prop_name
WHERE     AC.prop_name = 'Min Amount Limit' AND
          (CC.client_id = '1' OR CC.client_id IS NULL)

Presuming that prop_name is a valid link between the tables, and is unique.

Upvotes: 0

Related Questions