连接表的显示结果,即使某些值不匹配
Access数据库连接表的显示结果,即使某些值不匹配
table contacts
--------------
id
surname
name
table relations
---------------
contact_id
relation_id
两个CONTACT_ID和relation_id的外键参照表联系人的ID
我想执行一个查询来获取如果当前联系人的关系存在,则联系人的姓/名和关系的姓/名称。如果它不存在,我想获取联系人的姓氏/姓名和关系字段的空白值。
所有这一切都在一个查询
编辑:
我用左加入。我正在使用VB.NET查询:
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:不支持加入表达。
他们说,在另一篇文章中指出: “访问不会让你使用WHERE子句中的常规联接当您使用左/右/ INNER在FROM子句连接这可能是故意让你买。更昂贵的软件。“ - (Is the join expression not supported by MS Access?)
这不完全是。从我试过的一些例子中得出结论:
访问不会让你使用外连接(左/右)连同一个或多个INNER JOINS。 John Carmack的名字我能做什么? 我想避免单独的选择查询。 请帮助...
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-访问。
这是稍有不同(两个左联接),但它工作:
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涉及外键关系(以从很多 - >一个方向)。
为了有一个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
应该给语法错误在MS Access。内连接必须在离开连接和嵌套连接之前需要括号。 – Arvo 2011-04-12 12:21:40
请参阅编辑问题 – 2011-04-12 13:18:11
是的,我也检查过。 (很久以前我没有使用过Access)。我会在几分钟内编辑。 – 2011-04-12 13:25:21
使用'LEFT JOIN' – 2011-04-12 11:54:24