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

[media.opslag]

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.

 

[Guest]

Have you tried changing "tbl2.[somefield] = " to "tbl2.[somefield] Is In "?

"(e-mail address removed)" schreef:

 

[Michel Walsh]

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