By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,137 Members | 2,242 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,137 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.

Mar 23 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
<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 ***
Mar 23 '06 #2

P: n/a
Please don't multi-post!

http://tc2.atspace.com/0012-UsingUse...m#MultiPosting

HTH,
TC (MVP Access)
http://tc2.atspace.com

Mar 23 '06 #3

P: n/a
TC
Please don't multi-post!

http://tc2.atspace.com/0012-UsingUse...m#MultiPosting

HTH,
TC (MVP Access)
http://tc2.atspace.com

Mar 23 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.