Ambiguous Outer join error 1

[amberlynn]

Hello,
I'm trying to build a report based on a query.
The query is based on 2 tables (Projects & Updates).
I want to see ALL projects, but only info from the most recent update associated with a project (there may be many).
I can make it work, but only showing Projects that have at least one associated update - as soon as I change it to show all Project records, I get an 'ambiguous outer joins' error.
Help!

Amber

 

[lespaul]

how about something like this (typed, not tested):

SELECT * FROM 
PROJECTS P
LEFT OUTER JOIN (SELECT project, max(updateDate) FROM Updates GROUP BY Project) U on P.Project = U.Project

Leslie

Have you met Hardy Heron?

 

[amberlynn]

Thanks Leslie.

Problem is, I need 2 other fields from the Updates table (update (txt) and name (txt)). Once I add these to the query, I have to group by them to make it work, and then I end up with every record in the Update table, instead of just the most recent...

 

[lespaul]

so add those fields to the inside query:

SELECT * FROM 
PROJECTS P
LEFT OUTER JOIN (SELECT project, update, [name], max(updateDate) FROM Updates GROUP BY Project, update, [name]) U on P.Project = U.Project

are there multiple updates on the same date? if so, you'll have to do it slightly differently. First you would need to decide which record you want to get if there are multiple updates.

Basically what you need to do is get all the data you need from the updates table in a single query. Then use that query as the JOIN portion.

Leslie

Have you met Hardy Heron?

 

[amberlynn]

I won't have multiple update records with the same date, my 2 test records have projectID = 1, different txt in Update field, different txt in Name field, and different dates.
When I use the following SQL:

SELECT *
FROM tbl_Projects AS P LEFT OUTER JOIN [SELECT ProjectID, update, max(updateDt) FROM tbl_Update GROUP BY ProjectID, update]. AS U ON P.ID_Project = U.ProjectID;

I get BOTH records returned, and I'm trying to only get the latest.

If I leave out my other 2 text fields, I get the correct record returning...

 

[lespaul]

how about this?:

SELECT U1.*, P.* FROM tbl_Update U1
INNER JOIN (SELECT ProjectID, Max(UpdateDt) As MaxUpdateDate FROM tbl_Update GROUP BY ProjectID) U2 ON U1.ProjectID = U2.ProjectID and U1.UpdateDt = U2.MaxUpdateDate
LEFT OUTER JOIN tbl_Projects P ON P.ProjectID = U1.ProjectID

 

[amberlynn]

I'm getting a syntax error (missing operator) when I run it, and the highlighted area is 'U1.P' <-from ON U1.ProjectID

 

[PHV]

SELECT *
FROM tbl_Projects AS P LEFT JOIN (
SELECT U.ProjectID, [update field], [name field], updateDt
FROM tbl_Update AS U INNER JOIN (
SELECT ProjectID, Max(updateDt) AS LastDate FROM tbl_Update GROUP BY ProjectID
) AS L ON U.ProjectID = L.ProjectID AND U.updateDt = L.LastDate
) AS R ON P.ID_Project = R.ProjectID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886