Join or Subquery?

[dmeckley]

I've got two tables, a Part table and a vendor table. Both vendors and
manufacturers are in the vendor table with ID and name fields. The vendors
and manufacturers are referenced in the Part table by vendor.ID.

I'd like to view all parts in the part table with vendor ID and name and
manuf. ID and name.

I can't join both Part.vendID and Part.manufID to vend.ID without getting
ambiguous join errors and correlated subquerries seem to filter my view.

Can anyone give me an idea (short of changing the db schema) how to approach
this.

Thank in advance,

Davem

 

[Gerald Stanley]

Try joining the Part table twice to the Vendor Table along
the lines of

SELECT P.vendId, V.[name] as vendorName, P.manufId,
M.[name] as manufacturerName, ...
FROM (Part P INNER JOIN Vendor V ON P.vendId = V.vendorId)
INNER JOIN Vendor M ON P.manufId = M.vendorId

Hope This Helps
Gerald Stanley MCSD