<me**********@gmail.com> wrote in message
news:11*********************@t31g2000cwb.googlegro ups.com...
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.
I'm not sure your intention is quite clear. You have a left join in the
first bit, so you want to show all the values in Table1 regardless of
whether you get a match (based on the two id's) in the other table. On the
other hand, you have want the somefield to be equal to the max, which would
imply there was already a match with the ids.
If you are working from Access (as opposed to using a Jet database with some
other software), you could always create multiple queries: one based on the
other. This makes it easier to understand. So perhaps, you do a "group by
and get max" on one and use this in another. If you really want to, though,
you can write it all in one hit and I think this is what you more or less
need:
' *** Start ***
SELECT A.*, B.*
FROM
(Table1 A INNER JOIN
(SELECT id1, id2, Max(somefield) AS MyMax
FROM Table2 GROUP BY id1, id2) AS X
ON A.id2=X.id2 AND A.id1=X.id1)
INNER JOIN
Table2 AS B ON
A.id2=B.id2 AND A.id1=B.id1
' *** End ***
Note that Access may alter this syntax to [my alias select]. square brackets
and dot. This is shown below:
' *** Start ***
SELECT A.*, B.*
FROM
(Table1 A INNER JOIN
[SELECT id1, id2, Max(somefield) AS MyMax
FROM Table2 GROUP BY id1, id2]. AS X
ON A.id2=X.id2 AND A.id1=X.id1)
INNER JOIN
Table2 AS B ON
A.id2=B.id2 AND A.id1=B.id1
' *** End ***
