Need help with a query

Hi Experts,
 There are 3 tables. BOM, Parts and VENDORLINECARD . I am getting ManufacturerPartNo from the Parts table.  When I join with Vendor I am getting 3 rows for the vendor. But I want all the vendors in the same line after joining. Please help.

Thanks.


 SELECT B.[BomMstID]
       ,B.[BomID]
       ,P.ManufacturerPartNo
       ,v.VendorID
       ,B.[OrderDate]
       ,B.[CreatedDate]
       ,B.[NeedByDate]
       ,B.[JobNum]
       ,B.[JobDate]
       ,B.[PartID]
       ,B.[QtyOrdered]
       ,B.[PriceCurrent]
       ,B.[PricePrevious]
       ,B.[SubAssemNum]
       ,B.[WearSparePartFlag]
       ,B.[PriceTotal]
       ,B.[PONum]
       ,B.[POCreatedDt]
       ,B.[RFQNum]
       ,B.[RFQCreatedDt]
       ,B.[RFQExpResponseDt]
       ,B.[CustomerID]
       ,B.[RevisionLvl]
       ,B.[QtyRequired]
       ,B.[QtyReceived]
       ,B.[ExpectedDelDt]
       
   FROM [BOM] B

   left outer join Parts P  on B.PartID=P.PartID
   left outer join VENDORLINECARD as V on v.ManufacturerPartNo =P.ManufacturerPartNo

   where B.CustomerID=73 and B.JobNum = '15041701  ' and B.BomID = 20 and B.RevisionLvl='1'


 After this query mu output is
 ------------------------------------------

 BomMstID BomID      ManufacturerPartNo            VendorID      OrderDate      CreatedDate      NeedByDate      
 -------------------------------------------------------------------------------------------------------------------------------------------------------------
 1594      20                    Q#15-017-4312 rev2                                                                                        
 1595      20                    Q#15-017-4312                                                                                            
 1596      20                    HUB SHC1023 1/2 .375-.5          19                                                                                                                                                
 1596      20                    HUB SHC1023 1/2 .375-.5           3                                                                                                                                                      
 1596      20                    HUB SHC1023 1/2 .375-.5          15
RadhaKrishnaKiJayaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>When I join with Vendor I am getting 3 rows for the vendor.

<very wild guesses>
There are three rows in the Parts table for PartID or ManufacturerPartNo, and there is another column that guarantees uniqueness that is missing in your JOIN..ON clause.
By any chance are there multiple vendors that have the same exact part number?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
What's a 'BOM'?  Perhaps you can't join to Parts on just PartId.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
@Jim:  
BOM = Build of Materials (I'm guessing)

@Radha
You have 3 different vendors (VENDORLINECARD ) for the same part Notice the different VendorID's)

Does part table also have a Vendor ID?  Adding that to your join should clear that up.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RadhaKrishnaKiJayaAuthor Commented:
Hi Jim,

Thank you for your reply. You are right. Multiple vendors have the same part #. BOM is my bill of materials table. It has all the information and also PartID. I am joining with Part so that I can get the ManufacturerPartNo. I am Joining with Vendor so that I can get all the vendors who is having that part.

Thanks.
0
RadhaKrishnaKiJayaAuthor Commented:
Hi Kyle,

Part table do not have VendorID. It has ManufacturerPartNo. Vendor table has also ManufacturerPartNo . I join it to get the Vendors.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
In that case, you'll have to JOIN ON both part number and vendor number.  Something like..

left outer join VENDORLINECARD as V on v.ManufacturerPartNo =P.ManufacturerPartNo AND v.VendorNo = p.VendorNo
0
RadhaKrishnaKiJayaAuthor Commented:
Right now I am getting this.

BomMstID BomID      ManufacturerPartNo            VendorID      OrderDate      CreatedDate      NeedByDate      
  -------------------------------------------------------------------------------------------------------------------------------------------------------------
 1594      20                    Q#15-017-4312 rev2                                                                                        
  1595      20                    Q#15-017-4312                                                                                            
  1596      20                    HUB SHC1023 1/2 .375-.5          19                                                                                                                                                
  1596      20                    HUB SHC1023 1/2 .375-.5           3                                                                                                                                                      
  1596      20                    HUB SHC1023 1/2 .375-.5          15


I want like this
-----------------------
BomMstID BomID      ManufacturerPartNo            Vendor1       vendor2    vendor3      OrderDate      CreatedDate      NeedByDate      
  -------------------------------------------------------------------------------------------------------------------------------------------------------------
 1594      20                    Q#15-017-4312 rev2                                                                                        
  1595      20                    Q#15-017-4312                                                                                                            
  1596      20                    HUB SHC1023 1/2 .375-.5       19                3                     15
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
RadhaKrishnaKiJaya - Please understand that experts here cannot connecto to your data source(s) and run queries, so anything that depends on schema or data being populated in a certain way you're going to have to spell out for us.

>I want like this
>BomMstID BomID      ManufacturerPartNo            Vendor1       vendor2    vendor3
This is a different question from what you originally asked us, as now with the Vendor1 .. Vendor 2 .. Vendor3 in addition to the original question we're talking a T-SQL PIVOT statement to pull this off.

I propose we stick with the original question, you can award points based on comment(s) that help you, and ask the PIVOT question as a separate EE question.

Thanks in advance.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
btw What's the logic for the row with Vendor 19 being in the desired return set, and not the other two rows?
0
RadhaKrishnaKiJayaAuthor Commented:
Hi Jim,

Thank you for your effort to help me. But I am not asking anything different then what I have asked in the begining.

This is my original question.

"When I join with Vendor I am getting 3 rows for the vendor. But I want all the vendors in the same line after joining."

I gave the example so that it will be easier for the experts to understand. Because I know it is really difficult to work on other's query.

Thank u.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
I would recommend using a stuff / for xml path to comma delimit the list of vendors.
 SELECT B.[BomMstID]
       ,B.[BomID]
       ,P.ManufacturerPartNo 
       ,STUFF(
(
SELECT ',' + cast(vendorID as varchar(500)) 
FROM  VENDORLINECARD where ManufacturerPartNo = P.ManufacturerPartNo ORDER BY vendorName FOR XML PATH('')), 1,1,'') ) Vendors,
       ,B.[OrderDate]
       ,B.[CreatedDate]
       ,B.[NeedByDate]
       ,B.[JobNum]
       ,B.[JobDate]
       ,B.[PartID]
       ,B.[QtyOrdered]
       ,B.[PriceCurrent]
       ,B.[PricePrevious]
       ,B.[SubAssemNum]
       ,B.[WearSparePartFlag]
       ,B.[PriceTotal]
       ,B.[PONum]
       ,B.[POCreatedDt]
       ,B.[RFQNum]
       ,B.[RFQCreatedDt]
       ,B.[RFQExpResponseDt]
       ,B.[CustomerID]
       ,B.[RevisionLvl]
       ,B.[QtyRequired]
       ,B.[QtyReceived]
       ,B.[ExpectedDelDt]
       
   FROM [BOM] B
   left outer join Parts P  on B.PartID=P.PartID
   where B.CustomerID=73 and B.JobNum = '15041701  ' and B.BomID = 20 and B.RevisionLvl='1'

Open in new window

0
RadhaKrishnaKiJayaAuthor Commented:
Hi Kyle,

Thank you very much for trying to help me. But I am having hard time understanding the query. So I will try to find a different approach to solve the problem. But I really appreciate your effort.

Thank you again.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Radha - It appears that you are asking for a T-SQL PIVOT statement, where your results are grouped by part numbers for rows, but then vendors are columns.

Do us a favor and eyeball the above link, then confirm for us that that's the layout you're trying to accomplish.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RadhaKrishnaKiJayaAuthor Commented:
Thank you very much for the link. This is exact what I wanted. I tried it and it is working.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.