LEFT OUTER JOIN in SQL Server CE

Question

我怎么能加入两个表格,在服务器CE中各栏。

我有两个表格:

* E/CN.6/2009/1。

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

表Description

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

我要谈的是:

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

如<代码> 编码栏内填充时,该代码应为<>->-,但该代码未填入<代码>。

我尝试了这项法典:

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      +
+-----+----------+-----------------+

而且,当我试图提出《欧安条款》时:

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

登上了<代码>ID的行文,如:

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

我如何能够这样做?

<>>>

我尝试了这一法典,并努力:

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

But now, how can I add a WHERE clause on it (pls see table SCHEDULE above)?

我尝试:

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 in SQL Server CE

You need to use LEFT OUTER JOIN to join the tables Schedule and Description on the key field ID. Also, use COALESCE to replace NULL values in Description column with -

Script:

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

Output:

载于<>Microsoft Profour CE 版本 4.0.8482.1

“output”/

With WHERE clause

You need add the WHERE clause after the JOINs. If you are planning to having an ORDER BY, it should come after the WHERE clause.

Script:

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)

Output:

载于<>Microsoft Profour CE 版本 4.0.8482.1

“output

Answer 2

页: 1 LEFT OU JOIN 。

54 LLFiddle:

学历:

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 means select only rows that exist in both tables.
FULL OUTER JOIN means select rows as long as they exist in one table.

页: 1 FUL OUTER JOIN

Answer 4

页: 1 OUTER JOIN undINNER/code>, and use IsNull or Coalesce 改为NUL:



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

LEFT OUTER JOIN in SQL Server CE

Script:

Output:

With WHERE clause

Script:

Output:

友情链接