'Joining two tables and getting values

I have two config tables. The structure is as below:

Table 1: Client_Config
id, name, value, type, description

Table 2: App_Config
name, value, type, description

I want to get name and value from Client_config table where id = @id.

I also want to get name and values from App_config for rows where there are no entries(matched with name) in client_config. Values for the same name can be different in both the tables.

eg: Values in Client_Config

1, testName, testValue, testType, testDescription
1, testName1, testValue1, testType1, testDescription1
1, testName2, testValue2, testType2, testDescription2

Values in App_Config

testName, testValue1, testType, testDescription
testName1, testValue1, testType1, testDescription1
testName2, testValue2, testType2, testDescription2
testName3, testValue3, testType3, testDescription3

In the result set I need the following rows:

1, testName, testValue, testType, testDescription
1, testName1, testValue1, testType1, testDescription1
1, testName2, testValue2, testType2, testDescription2
NULL, testName3, testValue3, testType3, testDescription3
sqlsql-serversql-server-2008join


Solution 1:[1]

You can do it using a left join:

SELECT t.id, s.name, s.value, s.type, s.description
FROM  App_Config s
LEFT JOIN Client_Config t
 ON(t.name = s.name and t.id = @id)

Solution 2:[2]

You can try a query like below

select 
   c.id, a.name, a.value, a.type, a.description 
from  App_Config a
left join 
(
 select * from Client_Config where id=@id
)c
on c.name=a.name

Explanation: We need all rows from app_config and corresponding id from client_config. So we do a **LEFT JOIN** from A to C. The C result set however must contain rows from a particular @id only so we sneak in a WHERE clause in the C set

Sql fiddle demo link : http://sqlfiddle.com/#!6/44659/4

Solution 3:[3]

You can do it using a UNION ALL operation:

DECLARE @id INT = 1

SELECT id, name, value, type, description
FROM Client_Config
WHERE id = @id

UNION ALL

SELECT NULL, name, value, type, description
FROM App_Config AS ac
WHERE NOT EXISTS (SELECT 1 
                  FROM Client_Config AS cc
                  WHERE cc.name = ac.name AND cc.id = @id)

Demo here

Solution 4:[4]

With a Left Join you get all the rows from the left table and all the corresponding rows from the right table. If there is no matching information, then the columns of the right table will be NULL. Take a look at this useful diagram: SQL Join Diagrams

In particular, your query can be something like this:

SELECT c.id, a.name, a.value, a.type, a.description
FROM App_Config a
LEFT JOIN Client_Config c
ON c.name = a.name
WHERE c.id = @id

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 Giorgos Betsos
Solution 4

Related Questions