access join query problems

[thedigit]

hi,
I'm a new user here so please go easy on me

I have a query, which works fine:

SELECT au.UserId, au.FirstName, au.Surname, r.Percentage, r.ProjectId, r.ProjectVersion
FROM appuser AS au LEFT JOIN resources AS r ON (au.UserId = r.UserId and r.projectid=24 and r.projectversion=39
WHERE (((au.UserId) In (3,5,2,6)));

but what I actually want, is this:

SELECT au.UserId, au.FirstName, au.Surname, r.Percentage, r.ProjectId, r.ProjectVersion
FROM appuser AS au LEFT JOIN resources AS r ON (au.UserId = r.UserId and r.projectid=24 and r.projectversion=select max(projectversion) from r where projectid=24)
WHERE (((au.UserId) In (3,5,2,6)));

access doesnt seem to like it, but I'm sure it can be done in sql.
Thanks

 

[AlexCuse]

This part:

r.projectversion=select max(projectversion) from r where projectid=24

should be in your where clause not your join.

Try this:

SELECT au.UserId, au.FirstName, au.Surname, r.Percentage, r.ProjectId, r.ProjectVersion
FROM appuser AS au LEFT JOIN resources AS r ON (au.UserId = r.UserId and r.projectid=24) 
WHERE (((au.UserId) In (3,5,2,6))) and 
(r.projectversion=(select max(projectversion) from r where projectid=24));

[not tested]

Hope this helps,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[thedigit]

Thanks but I'm afraid that's not what I'm looking for.

I wanted the

r.projectversion=(select max(projectversion) from r where projectid=24

on the left join, so that any values of 3,5,2,6 that were not matching would have null rows.

I hope that makes sense. Access is frustrating me so much

 

[AlexCuse]

I just realized that I left this in the wrong place in the edited query:

and r.projectid=24

I don't believe those statements are valid for joins because they only reference one table. You could try something like this:

SELECT au.UserId, au.FirstName, au.Surname, r.Percentage, r.ProjectId, r.ProjectVersion

FROM appuser AS au LEFT JOIN resources AS r ON 
(au.UserId = r.UserId and r.projectid=24) 
[COLOR=red]LEFT JOIN resources AS r1 on  
(r.projectversion=(select max(projectversion) from r1 where projectid=24))[/color]

WHERE (((au.UserId) In (3,5,2,6))) and (r.projectid=24);

[Test it, I hate typing code in these windows]

However, after making the necessary correction the query above should be returning 'null rows' as well where there is no match.

Hope this helps, and if not please post a more comprehensive explanation of what you're trying to get done (sometimes code does not explain so well).

Alex



Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.