Left Join Not Supported - translated from legal SQL statement

[Ryan]

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*';

[pietlinden]

Data_Extract.Dealer_Code = 8494 --Problem occurs on this line in
Access

that line belongs in the WHERE clause. It's not a join.

[Sylvain Lafontaine]

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

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*';

[David W. Fenton]

pi********@hotmail.com wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

Data_Extract.Dealer_Code = 8494 --Problem occurs on this line in
Access

that line belongs in the WHERE clause. It's not a join.

It's not a join because it does not equate the values in two fields
in two different tables.

The Jet query otpimizer actually does treat real JOINS and the
equivalent WHERE clauses the same -- you can remove all joins and
replace them with criteria and see no change in performance.

But, again, in this case, using a constant value is not a join at
all. I didn't know that any SQL dialect allowed that in the FROM
clause.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Ryan]

It IS a join and is a perfectly legal SQL statement, although MS Access
does not allow it. Adding this to the 'where' clause is the easy and
normal route to take. If this was examined in detail (in both Access
and SQL) then it would show that having this in the where clause
excludes the data I need in the left join too early in the execution
process and therefore does not show this in the result set. In effect,
this excludes certain data and renders a left join defunct in this
instance.

I have discussed this on a SQL forum ( see
http://groups.google.com/group/comp....ffc15f7e22eca0
), and the easy answer (from a SQL point of view) is to include this in
the JOIN statement. Access does not support this, so back to the
original question.

Using my original SQL statement, the dealers with 8494 in the
Data_Extract table are excluded first, and when the join is performed,
the 'NULL' records from the left hand side of the join are excluded. To
be honest, I was suprised at this, so have re-done this several times,
plus asked another developer to do the same. Both of us had the same
results. We then did the same in SQL and the same happens, which I now
realise is expected, but not what I want due to how the statement is
executed. OK, so we can find a solution in SQL, but I need an Access
version if possible.

How can I work around the limitation of Access in such an instance ? If
Access does not indeed support this, then what alternatives are there
as the end result is not correct ?

[teddysnips]

It's perfectly legal in SQL Server.

Edward

[Larry Linson]

<te********@hotmail.com> wrote

It's perfectly legal in SQL Server.

That is immaterial when it comes to whether particular SQL is legal for the
Jet database engine. There are a number of other things valid in SQL Server
but not in Jet. Jet, like other DB engines, has its own "dialect" of SQL; it
has more differences from the SQL standart than some, and fewer differences
than others.

Larry Linson
Microsoft Access MVP

[lylefair]

Perhaps:

SELECT
L.LineDesc,
D.*
FROM
RDOData_Extract_Lines AS L
LEFT JOIN
[SELECT * FROM RDOData_Extract WHERE Dealer_Code = 8494]. AS D
ON
L.LineNum = D.Line_No
WHERE
L.LineNum NOT LIKE 'LAN%'

?

[Ryan]

Once again, missing the point. I know that it is legal in SQL and
illegal in Access and that Access/Jet has a 'different' interpretation
of this. I'm not interested in the dispute over the differences between
the two as I have already established that there are. What I want to
know is how to create the equivalent statement in SQL.

[Ryan]

Lylefair. Cool ! That works.

Thanks

Ryan

[David W. Fenton]

"Ryan" <ry********@hotmail.com> wrote in
news:11**********************@z14g2000cwz.googlegr oups.com:

It IS a join and is a perfectly legal SQL statement, . . .
Well, I'm certainly no SQL expert, but it's not joining two tables
by matching values in two fields in the respective tables, so the
use of the word "JOIN" makes little sense to me, even if there are
SQL dialects that support it.
. . . although MS
Access does not allow it. Adding this to the 'where' clause is the
easy and normal route to take. If this was examined in detail (in
both Access and SQL) then it would show that having this in the
where clause excludes the data I need in the left join too early
in the execution process and therefore does not show this in the
result set. In effect, this excludes certain data and renders a
left join defunct in this instance.

Well, then, use nested SQL. Replace the table in the FROM clause
with a SQL statement that filters the table you're trying to filter.
In most SQL dialects you need only surround that SQL statement with
parentheses, but Access has its own idiosyncratic format:

FROM [SQL Statemet]. As Alias

(and the alias can be the same as the name of underlying table
you're filteringn so that the SELECT will remain valid)

The only problem is that the SQL inside the brackets cannot itself
include any brackets. If you've used spaces or non-alphanumeric
characters in field/table names, then this won't work.

I don't know the official name for this technique, but I call it a
"virtual table."

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

te********@hotmail.com wrote in
news:11**********************@z14g2000cwz.googlegr oups.com:

It's perfectly legal in SQL Server.

That doesn't mean it's valid SQL. It just means SQL Server's dialect
of SQL allows it.

Jet has the DISTINCTROW predicate, but it's not standard SQL. But
it's part of Jet's dialect of SQL.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[david epsom dot com dot au]

> FROM [SQL Statemet]. As Alias

That's Jet 3.5 isn't it? I think Jet 4.0 is different.

(david)

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@216.196. 97.142...

"Ryan" <ry********@hotmail.com> wrote in
news:11**********************@z14g2000cwz.googlegr oups.com:

It IS a join and is a perfectly legal SQL statement, . . .

Well, I'm certainly no SQL expert, but it's not joining two tables
by matching values in two fields in the respective tables, so the
use of the word "JOIN" makes little sense to me, even if there are
SQL dialects that support it.

. . . although MS
Access does not allow it. Adding this to the 'where' clause is the
easy and normal route to take. If this was examined in detail (in
both Access and SQL) then it would show that having this in the
where clause excludes the data I need in the left join too early
in the execution process and therefore does not show this in the
result set. In effect, this excludes certain data and renders a
left join defunct in this instance.

Well, then, use nested SQL. Replace the table in the FROM clause
with a SQL statement that filters the table you're trying to filter.
In most SQL dialects you need only surround that SQL statement with
parentheses, but Access has its own idiosyncratic format:

FROM [SQL Statemet]. As Alias

(and the alias can be the same as the name of underlying table
you're filteringn so that the SELECT will remain valid)

The only problem is that the SQL inside the brackets cannot itself
include any brackets. If you've used spaces or non-alphanumeric
characters in field/table names, then this won't work.

I don't know the official name for this technique, but I call it a
"virtual table."

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Larry Linson]

"Ryan" wrote

Once again, missing the point. I know
that it is legal in SQL and illegal in
Access and that Access/Jet has a
'different' interpretation of this. I'm
not interested in the dispute over
the differences between the two as
I have already established that there
are.
My, my, we're getting a little snippy, aren't we? It's not nice to be snippy
with people you are asking to answer your questions. You may irritate them
to the point that they just let you try to find someone else to answer it.

In this newsgroup, most of us (other than the Resident Troll and his Legion
of Sockpuppets of Many Names) are here to answer questions, not to argue
with people who just don't like the correct answers they've gotten.
. . . What I want to know is how to
create the equivalent statement in SQL.

If you want precise answers, you need to be precise in your questions. That
is, do you want SQL Server dialect of SQL or Jet dialect of SQL? You seem to
use SQL in multiple ways.

In _Jet_ SQL, Piet has already told you that you need to move
Data_Extract.Dealer_Code = 8494 to the WHERE clause, but you argued about
that perfectly valid answer.

And, it seems to me you told us that it was legal in SQL Server, so you
appear to already know that answer. Furthermore, this is not the place for
Q&A about SQL Server's dialect of SQL.

Is it that you are determined to disregard the differences in SQL between
SQL Server and Jet and you are determined that you are going to force Jet to
accept it in the Join? Or is it, perhaps, that your imprecise use of "SQL"
is just confusing us?

Larry Linson

[Larry Linson]

"Ryan" wrote

Lylefair. Cool ! That works.

Lyle's always been cool. He's so cool they named a town after him in Texas,
very likely even before he was born. It's about halfway between Dallas and
Houston on Interstate 45.

Interesting that you liked his moving it to a WHERE clause, but didn't like
Piet's telling you to do that. Oh, well, life is full of little puzzlements.