[SQL] Ambiguous outer join

[pelicanstuff]

Just wondering if anyone can help me avoid the "Ambiguous outer join" error in the following bit of SQL:

TRANSFORM Count(Tbl_events.DateTime) AS CountOfDateTime
SELECT Tbl_Grid.GridSquare
FROM (Tbl_Grid LEFT JOIN Tbl_Locations ON Tbl_Grid.ID = Tbl_Locations.GridSquare)
INNER JOIN Tbl_events ON Tbl_Locations.ID = Tbl_events.Tbl_Locations_ID
GROUP BY Tbl_Grid.GridSquare
ORDER BY Tbl_Grid.GridSquare
PIVOT Hour([DateTime]);

[Stewart Ross]

Just wondering if anyone can help me avoid the "Ambiguous outer join" error in the following bit of SQL:

TRANSFORM Count(Tbl_events.DateTime) AS CountOfDateTime
SELECT Tbl_Grid.GridSquare
FROM (Tbl_Grid LEFT JOIN Tbl_Locations ON Tbl_Grid.ID = Tbl_Locations.GridSquare)
INNER JOIN Tbl_events ON Tbl_Locations.ID = Tbl_events.Tbl_Locations_ID
GROUP BY Tbl_Grid.GridSquare
ORDER BY Tbl_Grid.GridSquare
PIVOT Hour([DateTime]);

Hi Pelicanstuff. Joins need to be of the same type to avoid the error message - the mix of Left (or Right) joins with equi-joins causes the ambiguity. If left-joining Tbl_events is not possible (resulting in inclusion of other rows than the ones you want, say) you should reframe the query, separating the left joined part into a different query then using this new query to feed the crosstab, equi-joining to tbl_events. This keeps the consistency of the joins throughout.

Cheers

Stewart