The column prefix 'D' does not match with a table name or alias name u

[77amie77]

Help!!!

I have the below query which returns the following error:

The column prefix 'D' does not match with a table name or alias name used in the query.

SELECT V.ViolationID
FROM VMViolationsView VV, Violation V LEFT OUTER JOIN
Class C3 ON C3.ClassId = V.ClassID LEFT OUTER JOIN
ASSET ON V.ASSETID = ASSET.ASSETID LEFT OUTER JOIN
Deal D ON V.DealID = D.DealID AND V.Generation = D.Generation LEFT OUTER JOIN
WorkflowState WS ON V.Status = WS.Code INNER JOIN
DealControl DC ON D.DealID = DC.DealID INNER JOIN
Product P ON DC.ProductID = P.ProductID INNER JOIN
Customer C ON D.CustID = C.CustID LEFT OUTER JOIN
Location L ON L.LocationID = D.LocationID LEFT OUTER JOIN
Organisation O ON O.OrgID = D.OrigBranchID, Checks C1 LEFT OUTER JOIN
Checks C2 ON C2.CheckID = C1.TemplateRef LEFT OUTER JOIN
UserDisplayName UDN ON UDN.UserID = D.ActionUserID



If I modify the positon of the last LEFT OUTER JOIN and position it directly after where the Deal table is initially joined as shown below, the query works. Can anyone please explain this to me. Am I missing something fundamental here.

SELECT V.ViolationID
FROM VMViolationsView VV, Violation V LEFT OUTER JOIN
Class C3 ON C3.ClassId = V.ClassID LEFT OUTER JOIN
ASSET ON V.ASSETID = ASSET.ASSETID LEFT OUTER JOIN
Deal D ON V.DealID = D.DealID AND V.Generation = D.Generation LEFT OUTER JOIN
UserDisplayName UDN ON UDN.UserID = D.ActionUserID LEFT OUTER JOIN
WorkflowState WS ON V.Status = WS.Code INNER JOIN
DealControl DC ON D.DealID = DC.DealID INNER JOIN
Product P ON DC.ProductID = P.ProductID INNER JOIN
Customer C ON D.CustID = C.CustID LEFT OUTER JOIN
Location L ON L.LocationID = D.LocationID LEFT OUTER JOIN
Organisation O ON O.OrgID = D.OrigBranchID, Checks C1 LEFT OUTER JOIN
Checks C2 ON C2.CheckID = C1.TemplateRef

If it would help to have the table definitions I could script them up.

Thanks in advance

Jamie

 

[nigelrivett]

Organisation O ON O.OrgID = D.OrigBranchID, Checks C1

Try putting a join to Checks C1 rather than the ,

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.