Re: jet sql access subquery in from clause left outer join does not work ... help!!

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi,


That looks quite unusual. Try to push that condition between a table and a
constant from the ON clause to a WHERE clause.

SELECT
tbl1.[field],
tbl2.[otherfield]

FROM [Table1] tbl1
left outer join [Table2] tbl2 on
tbl2.[id1] = tbl1.[id1] and
tbl2.[id2] = tbl1.[id2]

WHERE
tbl2.[somefield] =
(
SELECT MAX([somefield])
FROM [Table2] tbl2_temp
WHERE tbl2_temp.[id1] = tbl1.[id1] and
tbl2_temp.[id2] = tbl1.[id2]
)


That is what Jet would produce anyhow. Your case is very similar to the
"Iqaluit trauma" :


SELECT a.city
FROM authors As a
LEFT JOIN titleauthor As b
ON a.au_id = b.au_id
AND
a.city='Iqaluit'



returns nothing under Jet, but all the rows from authors in MS SQL Server:


Menlo Park
Oakland
Berkeley
San Jose
Oakland
Lawrence
Berkeley
Palo Alto
Covelo
San Francisco
Nashville
Corvallis
Walnut Creek
Ann Arbor
Gary
Oakland
Oakland
Oakland
Rockville
Palo Alto
Vacaville
Salt Lake City
Salt Lake City


even if NONE of these record have city= 'Iqaluit'. That may hurt your
concept of SQL being logical (since you asked, in the ON clause,
a.city='Iqaluit', while you get 'Menlo Park' and other cities) but remember
that a "preserved" table should keep all the records through the JOIN, and
so, while Jet result is intuitive, it is "wrong" (but classical) and while
MS SQL Server can be traumatic, it is right, accordingly to the definitions.
Anyhow...



To get the standard behavior from Jet, use a subquery:


SELECT * FROM authors WHERE a.city='Iqaluit'


and save it. Then

SELECT a.city
FROM savedQuery As a LEFT JOIN titleAuthor As b ON a.au_id=b.au_id


will do the job.


To get the Jet behavior from MS SQL Server, as we did, move the condition
between a table and a constant into the WHERE clause.



Hoping it may help,
Vanderghast, Access MVP



<media.opslag@xxxxxxxxx> wrote in message
news:1143107138.583467.127310@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi,

How can i get this to work in access / jet sql ??? Someone??

SELECT
tbl1.[field],
tbl2.[otherfield]

FROM [Table1] tbl1
left outer join [Table2] tbl2 on
tbl2.[id1] = tbl1.[id1] and
tbl2.[id2] = tbl1.[id2] and
tbl2.[somefield] =
(
SELECT MAX([somefield])
FROM [Table2] tbl2_temp
WHERE tbl2_temp.[id1] = tbl1.[id1] and
tbl2_temp.[id2] = tbl1.[id2]
)

It seems access doesn't have the possibility to put subqueries in from
clauses - left outer joins...

Thanx!
Gene.



.

Relevant Pages

  • Re: Two counts in one query
    ... subquery starts out just returning the values for each row in table2, ... Again, I have some doubts this would work in Jet, but (if I haven't ... made any mistakes) it is close to what to write for MSDE and SQL ... If you're using MSDE or SQL Server, ...
    (microsoft.public.access.queries)
  • Re: Better "Join" vs "Where" clause?
    ... results returned are different from Jet than within SQL Server. ... the JOIN clause rather than the WHERE clause. ... outer join criteria in the WHERE clause in SQL Server (not ...
    (microsoft.public.access.queries)
  • Re: Not In and Not Exists
    ... you did not specify WHERE condition in the IN clause as you specify it in ... EXISTS clause and as result we got nothing. ... > SQL Server will try to optimize query plans so neither technique ought to ... > INSERT INTO Table2 VALUES ...
    (microsoft.public.sqlserver.programming)
  • Re: Which comes first criteria or Join...
    ... I am sure there is a way to run it, with Jet, but I ... So, with MS SQL Server, really, the ON clause is evaluated first, then, ... was not possible with the ANSI-89 syntax. ...
    (microsoft.public.access.queries)
  • Re: Trying to understand the Access datasource processing and overhead when using filter to select a
    ... Access with SQL Server "upgrade" warn that, more often than not, it will be ... running, not walking, to their SQL Server DB to add that clause. ... With Jet, you do have control. ... there's often a DBA between ...
    (comp.databases.ms-access)