不支持左连接 - 从合法的SQL语句翻译 [英] Left Join Not Supported - translated from legal SQL statement
问题描述
我想在Access中执行以下SQL语句。但是，它不会允许我使用我的加入声明的次要部分并告诉我
这是不支持的。好的，所以Access不支持它，但是
必须是另一种选择。
我可以添加部分''D.Dealer_Code''到where子句，但因为
执行语句的方式，它排除了我想要的数据。
我的SQL语句完全是leagal并且工作正常，但是访问权限是有限的。
限制。那么，关于如何在Access中进行此操作的任何想法？
Ta
Ryan
适用于SQL的版本。
SELECT
L.LineDesc，
D. *
FROM
RDOData_Extract_Lines L
LEFT JOIN RDOData_Extract D
ON L.LineNum = D.Line_No AND
D.Dealer_Code = 8494
WHERE
L.LineNum不喜欢''LAN％''
在Access中无法使用的版本
SELECT
Data_Extract_Lines.LineDesc，
D. *
FROM
Data_Extract_Lines
LEFT JOIN Data_Extract
ON Data_Extract_Lines.LineNum = Data_Extract.Line_No AND
Data_Extract.Dealer_Code = 8494 - 问题发生在这一行上
访问
WHERE
Data_Extract_Lines.LineNum不喜欢局域网*;
I want to do the following SQL statement in Access. However, it won''t
allow me to have the secondary part of my join statement and tells me
that this is not supported. OK, so Access doesn''t support it, but there
must be an alternative.
I can add the part ''D.Dealer_Code'' to the where clause, but because of
the way that the statement is executed, it excludes data that I want.
My SQL statement is perfectly leagal and works fine, but Access is
limited. So, any ideas on how to approach this in Access ?
Ta
Ryan
Version that works in SQL.
SELECT
L.LineDesc,
D.*
FROM
RDOData_Extract_Lines L
LEFT JOIN RDOData_Extract D
ON L.LineNum = D.Line_No AND
D.Dealer_Code = 8494
WHERE
L.LineNum NOT LIKE ''LAN%''
Version that doesn''t work in Access
SELECT
Data_Extract_Lines.LineDesc,
D.*
FROM
Data_Extract_Lines
LEFT JOIN Data_Extract
ON Data_Extract_Lines.LineNum = Data_Extract.Line_No AND
Data_Extract.Dealer_Code = 8494 --Problem occurs on this line in
Access
WHERE
Data_Extract_Lines.LineNum NOT LIKE ''LAN*'';
推荐答案
Data_Extract.Deal er_Code = 8494 - 问题出现在这一行中
访问
该行属于WHERE子句。这不是一个加入。
Data_Extract.Dealer_Code = 8494 --Problem occurs on this line in
Access
that line belongs in the WHERE clause. It''s not a join.
我记得我可以通过左右加入解决我的大部分麻烦
在Access中使用子查询。您应该尝试这个条件吗？
Data_Extract.Dealer_Code = 8494？。
此查询可以是同一查询或另一个查询中的命名子查询; as
Access可让您以与
视图相同的方式使用其他查询。
-
Sylvain Lafontaine，ing。
MVP - 技术虚拟PC
电子邮件： http://cerbermail.com/?QugbLEWINF
" Ryan" < RY ******** @ hotmail.com>在消息中写道
news：11 ********************** @ g43g2000cwa.googlegr oups.com ...
I remember that I could solve most of my troubles with Left and Right Join
in Access by using a subquery. You should try this for the condition ?
Data_Extract.Dealer_Code = 8494 ?.
This query could be a named subquery in the same query or another query; as
Access give you the possibility of using other queries in the same way as a
view.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Ryan" <ry********@hotmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
我想在Access中执行以下SQL语句。但是，它不允许我拥有我的加入声明的次要部分，并告诉我
这不受支持。好的，所以Access不支持它，但是
必须是另一种选择。
我可以将部分''D.Dealer_Code''添加到where子句中，但是因为
语句执行的方式，它排除了我想要的数据。
我的SQL语句完全是leagal并且工作正常，但Access受限制。那么，关于如何在Access中实现这一点的任何想法？
Ryan
在SQL中工作的版本。
SELECT
L.LineDesc，
来自
RDOData_Extract_Lines L
LEFT JOIN RDOData_Extract D
ON L.LineNum = D.Line_No AND
D.Dealer_Code = 8494
L.LineNum不喜欢''LAN％''
版本没有''在Access中工作
SELECT
Data_Extract_Lines.LineDesc，
来自
Data_Extract_Lines
LEFT JOIN Data_Extract
ON Data_Extract_Lines.LineNum = Data_Extract.Line_No AND
Data_Extract.Dealer_Code = 8494 - 问题发生在这一行的
访问
Data_Extract_Lines.LineNum不喜欢'LAN *'';
I want to do the following SQL statement in Access. However, it won''t
allow me to have the secondary part of my join statement and tells me
that this is not supported. OK, so Access doesn''t support it, but there
must be an alternative.
I can add the part ''D.Dealer_Code'' to the where clause, but because of
the way that the statement is executed, it excludes data that I want.
My SQL statement is perfectly leagal and works fine, but Access is
limited. So, any ideas on how to approach this in Access ?
Ta
Ryan
Version that works in SQL.
SELECT
L.LineDesc,
D.*
FROM
RDOData_Extract_Lines L
LEFT JOIN RDOData_Extract D
ON L.LineNum = D.Line_No AND
D.Dealer_Code = 8494
WHERE
L.LineNum NOT LIKE ''LAN%''
Version that doesn''t work in Access
SELECT
Data_Extract_Lines.LineDesc,
D.*
FROM
Data_Extract_Lines
LEFT JOIN Data_Extract
ON Data_Extract_Lines.LineNum = Data_Extract.Line_No AND
Data_Extract.Dealer_Code = 8494 --Problem occurs on this line in
Access
WHERE
Data_Extract_Lines.LineNum NOT LIKE ''LAN*'';
pi********@hotmail.com 写在
新闻：11 ******************** **@o13g2000cwo.googlegr oups.co m：
pi********@hotmail.com wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:
Data_Extract.Dealer_Code = 8494 - 问题出现在这一行的
该行属于WHERE子句。它不是一个连接。
Data_Extract.Dealer_Code = 8494 --Problem occurs on this line in
Access
that line belongs in the WHERE clause. It''s not a join.
它不是一个连接，因为它不等于两个字段中的值
in两个不同的表。
Jet查询otpimizer实际上确实对待真正的JOINS和
等效的WHERE子句相同 - 你可以删除所有连接和
用标准替换它们并且看不到性能的变化。
但是，再次，在这种情况下，使用常量值不是
的连接
全部。我不知道任何SQL方言都允许在FROM
条款中使用。
-
David W. Fenton http://www.bway.net/~dfenton
bway dot net的
dfenton http://www.bway.net/~dfassoc
这篇关于不支持左连接 - 从合法的SQL语句翻译的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
