显示联接表的结果，即使某些值不匹配

Question

Access Database

table contacts
--------------
id
surname
name

table relations
---------------
contact_id
relation_id

contact_id和relation_id都是表联系人id引用的外键

我想执行一个查询来获取联系人的姓/名和关系的姓/名(如果当前联系人的关系存在)。如果它不存在，我想获得联系人的姓氏/姓名和关系字段的空白值。

所有这些在一个查询中

编辑:

我使用左连接。我正在使用VB运行查询。净:

Dim myOleDbDataReader As OleDbDataReader = _
    New OleDbCommand( _
    "SELECT c.id           AS contact_id " & _
    "     , c.surname      AS contact_surname " & _
    "     , c.name         AS contact_name " & _
    "     , c2.id          AS related_id " & _
    "     , c2.surname     AS related_surname " & _
    "     , c2.name        AS related_name " & _
    "FROM ((contacts c " & _
    "LEFT JOIN relations r " & _
    "ON c.id = r.contact_id) " & _
    "INNER JOIN contacts c2 " & _
    "ON c2.id = r.relation_id)" _
    , connection).ExecuteReader()

我得到OleDbException: Join expression not supported.

他们在另一篇文章中说:当你在FROM子句中使用LEFT/RIGHT/INNER join时，Access将不允许你在where子句中使用常规连接。这可能是有意让你购买更昂贵的软件。"- (MS Access不支持连接表达式吗?)

事实并非如此。从我尝试的一些例子中，我得出结论:

Access不允许您将外连接(左/右)与一个或多个内连接()一起使用。我能以约翰·卡马克的名义做什么?我想避免单独的选择查询。请帮助…

Answer 1

SELECT c.id       AS contact_id
     , c.surname  AS contact_surname
     , c.name     AS contact_name
     , c2.id      AS related_id
     , c2.surname AS related_surname
     , c2.name    AS related_name
FROM contacts c
  LEFT JOIN relations r
    ON c.id = r.contact_id
  JOIN contacts c2
    ON r.relation_id = c2.id

上面的不工作在MS-Access

这有点不同(两个左连接)，但它工作:

SELECT c.id       AS contact_id
     , c.surname  AS contact_surname
     , c.name     AS contact_name
     , c2.id      AS related_id
     , c2.surname AS related_surname
     , c2.name    AS related_name
FROM contacts c
  LEFT JOIN
    ( relations r
      LEFT  JOIN contacts AS c2
        ON r.relation_id = c2.id
    )  
    ON c.id = r.contact_id

尽管第二个LEFT JOIN，它将给出相同的结果集，因为第二个LEFT JOIN涉及一个外键关系(从many -> one的方向)。

要使用LEFT JOIN和INNER JOIN，可以使用:

SELECT c.id       AS contact_id
     , c.surname  AS contact_surname
     , c.name     AS contact_name
     , g.id       AS related_id
     , g.surname  AS related_surname
     , g.name     AS related_name
FROM contacts c
  LEFT JOIN
    ( SELECT r.contact_id
           , c2.id      
           , c2.surname
           , c2.name 
      FROM relations r
        INNER JOIN contacts AS c2
          ON r.relation_id = c2.id
    )  AS g
    ON c.id = g.contact_id