我如何加入不同列中有两个表中的SQL Server CE？(How do I join two ta

我怎样才能加入不同列中有两个表中的SQL Server CE？

我有两个表：

表附表

+-----+----------+------+
+ ID  + Name     + Type +
+-----+----------+------+
+ 1   + A        + 1    +
+ 2   + B        + 1    +
+ 3   + C        + 2    +
+-----+----------+------+

表说明

+-----+--------------------+
+ ID  + Description        +
+-----+--------------------+
+ 1   + A1 - XXXXX         +
+-----+--------------------+

而我想要得到的是如表：

+-----+----------+-----------------+
+ ID  + Name     + Description     +
+-----+----------+-----------------+
+ 1   + A        + A1 - XXXXX      +
+ 2   + B        + -               +
+ 3   + C        + -               +
+-----+----------+-----------------+

当Description栏应填写-当ID是不是在Description表。

我想这样的代码：

SELECT S.ID, D.Description 
FROM Schedule AS S 
INNER JOIN Description AS D

但导致：

+-----+----------+-----------------+
+ ID  + Name     + Description     +
+-----+----------+-----------------+
+ 1   + A        + A1 - XXXXX      +
+ 2   + B        + A1 - XXXXX      +
+ 3   + C        + A1 - XXXXX      +
+-----+----------+-----------------+

当我试着给ON子句：

SELECT S.ID, D.Description 
FROM Schedule AS S 
INNER JOIN Description AS D ON S.ID = D.ID

它只是让行，其中ID是在Description表，如：

+-----+----------+-----------------+
+ ID  + Name     + Description     +
+-----+----------+-----------------+
+ 1   + A        + A1 - XXXXX      +
+-----+----------+-----------------+

我怎样才能做到这一点？

---

[UPDATE]

我想这个代码和它的工作原理：

SELECT  S.ID, S.Name, COALESCE (D.Description, '-') AS Description
FROM Schedule AS S 
LEFT OUTER JOIN Description AS D ON S.ID = D.ID

但现在，我怎么能添加上一个WHERE子句（请见表SCHEDULE以上）？

我试过了：

SELECT  S.ID, S.Name, COALESCE (D.Description, '-') AS Description
FROM Schedule AS S 
LEFT OUTER JOIN Description AS D ON S.ID = D.ID AND S.Type = '1'

但仍然得到完整行。

Answer 1:

LEFT OUTER JOIN在SQL Server CE

您需要使用LEFT OUTER JOIN联接表计划 ，并说明在关键领域的ID。 此外，使用COALESCE ，以取代在说明列NULL值-

脚本：

SELECT          S.ID
            ,   S.Name
            ,   COALESCE (D.Description, '-') AS Description
FROM            Schedule AS S 
LEFT OUTER JOIN Description AS D 
ON              S.ID = D.ID

输出：

经测试，在Microsoft SQL Server CE version 4.0.8482.1

随着WHERE子句

您需要添加WHERE中的JOIN后的条款。 如果您计划到具有ORDER BY ，它应该在后WHERE子句。

脚本：

SELECT          S.ID
            ,   S.Name
            ,   COALESCE (D.Description, '-') AS Description
FROM            Schedule    AS S 
LEFT OUTER JOIN Description AS D 
ON              S.ID = D.ID
WHERE           (S.Type = 1)

输出：

经测试，在Microsoft SQL Server CE version 4.0.8482.1

Answer 2:

您需要LEFT OUTER JOIN 。

SQLFiddle： http://sqlfiddle.com/#!3/7dccf/1

查询：

select s.ID, s.Name, Coalesce(d.description,'-') as description
from schedule s 
left outer join description d on d.id = s.id

Answer 3:

SELECT S.ID, D.Description 
FROM Schedule AS S FULL OUTER JOIN Description AS D
ON S.ID = D.ID

INNER JOIN手段只选择存在于两个表中的行。
FULL OUTER JOIN手段选择，只要他们在一个表中存在的行。

您需要FULL OUTER JOIN ...

Answer 4:

您需要使用一个OUTER JOIN而不是INNER ，并使用IsNull或Coalesce来代替NULL值：

SELECT S.ID, Coalesce(D.Description , '-') AS Description
FROM Schedule AS S FULL OUTER JOIN Description AS D
ON S.ID = D.ID

文章来源: How do I join two tables with different column in SQL Server CE?