Ambiguous Outer Join Error

[scruiks]

I have 3 tables (simplified):
Invoice Header
Invoice_No


Vendor

Invoice_Detail
Invoice_No
Item_No

Item Desc
Vendor
Item_No
Item_Description

I'm trying to find any Item_No in Invoice_Detail that does not have an Item_Description in Item_Desc. When I construct the query to join Invoice_Header.Vendor to Item_Desc.Vendor, Invoice_Header.Invoice_No to Invoice_Detail.Invoice_No and Invoice_Detail.Item_No to Item_Desc.Item_No where Item_Desc.Item_Description is null I get an error about ambiguous outer joins and to create a separate query to perform the first join, then include that uery in my SQL statement. I have no idea how to do this! Can anyone help?

[jzwp11]

If you are just looking for items in the invoice detail table and item description table, then just include those two tables in the query.

SELECT Item_Desc.Item_No, Item_Desc.Item_Description
FROM Item_Desc INNER JOIN Invoice_Detail on Item_Desc.Item_No=Invoice_Detail.Item_No
WHERE Item_Desc.Item_Description is null OR nz(Item_Desc.Item_Description,"")=""



Alternatively you could use a nested query approach, but generally using the IN operator can be a little slow.

SELECT Invoice_Detail.Invoice_No, Invoice_Detail.Item_No
FROM Invoice_Detail
WHERE Invoice_Detail.Item_No IN (SELECT Item_Desc.Item_No
FROM Item_Desc
WHERE Item_Desc.Item_Description is null OR nz(Item_Desc.Item_Description,"")="")

[scruiks]

Thank you jzwp11. The problem I'm having is that the same item# can exist for multiple vendors (with different descriptions), so I need to include the Vendor in the criteria. Your example gave me some ideas that I'm going to try however

It worked! I just included the vendor match in the nested query. Thanks so much!

[jzwp11]

You're welcome & I'm glad to hear that you came to a solution.