Complicated query won't allow OUTER JOIN

[BeanDog]

I have a very complicated query:
----------------------------
SELECT dbo.tblItemByUPC.upc, dbo.tblItemByUPC.pack,
dbo.tblItemByUPC.size, dbo.tblItemByUPC.description,
dbo.tblNegotiation.dateacceptedbyagent,
dbo.tblNegotiation.dateacceptedbyrespondent,
dbo.tblNegotiation.finalshippingquantity,
tblAgent.agentname AS respondentname,
tblDivision.divisionname, tblCompany.companyname,
dbo.tblItemNumber.itemnumber, dbo.tblItemNumber.netprice,
dbo.tblStatus.status,
dbo.tblShippingContainer.shippingcontainer,
dbo.tblInventoryLevel.inventorylevel,
dbo.tblNegotiation.negotiationID, dbo.tblNegotiation.finalprice,
tblAgent1.agentname,
tblCompany1.companyname AS agentcompany, tblDivision1.divisionname AS agentdivision,
dbo.tblNegotiation.lastofferorID, tblAgent1.isbuyer,
tblAd.agentID, dbo.tblLot.lotID, dbo.tblLot.lotdescription
FROM dbo.tblLot INNER JOIN
dbo.tblLotItems ON
dbo.tblLot.lotID = dbo.tblLotItems.lotID INNER JOIN dbo.tblItemByUPC ON
dbo.tblItemByUPC.upc = dbo.tblLotItems.upc RIGHT OUTER JOIN
dbo.tblNegotiation INNER JOIN
tblAd ON dbo.tblNegotiation.adID = tblAd.adID INNER JOIN
tblAgent ON
dbo.tblNegotiation.respondentID = tblAgent.agentID INNER JOIN
tblDivision ON
tblAgent.divisionID = tblDivision.divisionID INNER JOIN
tblCompany ON
tblCompany.companyID = tblDivision.companyID INNER JOIN
dbo.tblStatus ON
dbo.tblNegotiation.finalstatusID = dbo.tblStatus.statusID INNER JOIN
dbo.tblInventoryLevel ON
dbo.tblNegotiation.finalinventorylevelID = dbo.tblInventoryLevel.inventorylevelID
INNER JOIN
dbo.tblShippingContainer ON
dbo.tblNegotiation.finalshippingcontainerID = dbo.tblShippingContainer.shippingcontainerID
INNER JOIN
tblAgent tblAgent1 ON
tblAd.agentID = tblAgent1.agentID INNER JOIN
tblDivision tblDivision1 ON
tblAgent1.divisionID = tblDivision1.divisionID INNER JOIN
tblCompany tblCompany1 ON
tblDivision1.companyID = tblCompany1.companyID ON
dbo.tblLot.lotID = tblAd.lotID LEFT OUTER JOIN
dbo.tblItemNumber ON
dbo.tblItemNumber.upc = tblItemByUPC.upc AND
(dbo.tblItemNumber.itemnumberlistID = @pItemNumberListID OR
dbo.tblItemNumber.itemnumberlistID IS NULL)

WHERE COALESCE(dbo.tblLotItems.upc, tblAd.upc) = dbo.tblItemByUPC.upc AND

(tblAgent1.agentID = @pAgentID)

----------------------------
Now, if you're like me, you just skipped that code sample. Here's the problem. Somewhere in there it says:

ON dbo.tblLot.lotID = tblAd.lotID

Which is a continuation on an INNER JOIN phrase. When I changed that to

LEFT OUTER JOIN dbo.tblAd ON dbo.tblLot.lotID=tblAd.lotID

It gave me an error saying that tblAd was already declared in the query. Problem is, up there where I mention it the first time, it's an inner join! How do I fix this?

 

[MalcolmW]

Can you duplicate the problem with a simpler query? The bigger the bun, the smaller the meat looks

 

[BeanDog]

I finally figgered it out. Thanks for listening anyhoo.