连接两个表并获取值

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.

我想从Client_config表中获取名称和值，其中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.

我还想从App_config中获取在client_config中没有条目(匹配名称)的行的名称和值。相同名称的值在两个表中都可能不同。

eg: Values in Client_Config

例如:在Client_Config值

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

Values in App_Config

值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

4 个解决方案

#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)

#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

说明:我们需要来自app_config的所有行和来自client_config的相应id。因此，我们从a到C执行** *LEFT JOIN**，但是C结果集必须只包含来自特定@id的行，以便我们在C集中插入WHERE子句

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

Sql小提琴演示链接:http://sqlfiddle.com/#!6/44659/4

#3

You can do it using a UNION ALL operation:

您可以使用UNION ALL操作:

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

演示

#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

使用左连接，您可以从左表中获得所有的行，并从右表中获得所有相应的行。如果没有匹配信息，则正确表的列将为空。看看这个有用的图:SQL连接图

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

#1