Retrieving Specific Records From 3 Tables

[mikemcginty]

I am having great difficulty finding the correct SQL statement to retrieve data from only the following records (ie the maximum ID2 and ID3 records for each ID1) from these three tables.

ID1 ID2 ID3
1 4 3 + data
2 2 2 + data
3 3 3 + data
4 3 4 + data

Each Table has relevant data to retrieve per record
I have tried Group By and nested selects to no avail

Table 1 Table 2 Table 3
ID1 fID1 ID2 fID1 fID2 ID3
1 1 1 1 1 1
2 1 2 1 1 2
3 1 3 1 2 1
4 1 4 1 2 2
2 1 1 3 1
2 2 1 4 1
3 1 1 4 2
3 2 1 4 3
3 3 2 1 1
4 1 2 2 1
4 2 2 2 2
4 3 3 1 1
3 2 1
3 2 2
3 3 1
3 3 2
4 1 1
4 2 1
4 3 1
4 3 2
4 3 3
4 3 4


When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!

 

[mikemcginty]

Is this heading in the right direction (even though there is a syntax error)?

SELECT Table1.ID1, Table1.Certificate, Table1.Subject, Table1.OwnerName, Table2.ID2, Table2.Priority, Table2.Complete, Table2.Budget, Table2.Actual, Table2.Forecast, Table3.ID3, Table3.WorkStatus, Table3.ArchiveBox, Table3.DateIssued

FROM (Table1

INNER JOIN
(SELECT Max(ID2)
FROM Table2
GROUP BY Table2.fID1
HAVING Table2.fID1 = Table1.ID1) AS X
ON Table1.ID1 = X.fID1)

INNER JOIN
(SELECT Max(ID2), Max(ID3)
FROM Table3
GROUP BY Table3.fID1
HAVING (Table3.fID1 = X.fID1
AND Table3.fID2 = X.ID2) AS Y
ON (X.fID1 = Y.fID1 AND X.ID2 = Y.lID2)

GROUP BY Table1.ID1, Table1.Certificate, Table1.Subject, Table1.OwnerName, Table2.ID2, Table2.Priority, Table2.Complete, Table2.Budget, Table2.Actual, Table2.Forecast, Table3.ID3, Table3.WorkStatus, Table3.ArchiveBox, Table3.DateIssued

HAVING (MIN(Table2.Priority) >= 1
AND MAX(Table3.ID3) >= '19300102 00:00:00'
AND MAX(Table3.ID3) <= '20061024 23:59:59')

ORDER BY Max(Table3.ID3) DESC


24/10/2006 11:47:51 AM Error (-2147217900) Microsoft OLE DB Provider for SQL Server - Incorrect syntax near the keyword 'AS'.



When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!