PDA

View Full Version : SQL statement i can't figure out

TPnTX
04-01-03, 09:51
If anyone can easily do this I'd appreciate it. I'm scratching my head and burning time.

I have 4 tables all using Uinique ID's as primary key

Profile,PofileDetail, Vendorparts & Vendor.

I want to list all detail records that have a vendorpart where name like '%NLA' and display vendor name

Profile.UID
Profile.Number

ProfileDetail.UID
ProfileDetail.ProfileNumber
ProfileDetail.VenPartID

VendorParts.UID
VendorParts.PartName

Vendor.UID
Vendor.Name

Profile.Number VendorPart.PartName Vendor.Name
-----------------------------------------------------------------

I've come close to doing it but when I add the vendor.Name to the mix, all the vendor.PartName fields display the same PartName.
Thanks
r937
04-01-03, 10:00
i'm not sure how your tables are related
select ProfileDetail.ProfileNumber
, VendorParts.PartName
, Vendor.Name
from ProfileDetail
inner
join VendorParts
on ProfileDetail.VenPartID = VendorParts.UID
inner
join Vendor
on ??????????
rudy
http://r937.com/
TPnTX
04-01-03, 10:18
select ProfileDetail.ProfileNumber
, VendorParts.PartName
, Vendor.Name
from ProfileDetail
inner
join VendorParts
on ProfileDetail.VenPartID = VendorParts.UID
inner
join Vendor
on ??????????


You thats exactly what I've done except I didn't use the Inner/join/on
It's works fine doing the vendorpart join.

Oh I left one field out in the example. The VendorPart.VendorID field points to Vendor.UID. Thats where I have trouble trying to join it similar to the Detail-to-VendorPart join.

I'm trying you example now adding:
Inner
Join
on Vendor.UID = VendorParts.VendorID
TPnTX
04-01-03, 10:30
That did it!

I see to things that gave me trouble.

1 I wasn't using the join/inner/on. Actually I tried.

2
on ProfileDetail.VenPartID = VendorParts.UID

I would have thought the this to be
VendorParts.UID = ProfileDetail.VenPartID


Thanks though it works now.