Need help with a query

Hi Experts,

I have two tables.

table1
---------
Manu.Part no.               Description                                       Vendor1      Vendor2    Vendor3
--------------------------------------------------------------------------------------------------------------------------------
HUB SHC1023 1/2 .375-.5     CORD GRIP
Q#15-017-4312                RA Conveyor Extension
Q#15-017-4312 rev2          AOP50 Installation


table2
------------
Manu.Part no.                           VendorID
---------------------------------------------------------------
HUB SHC1023 1/2 .375-.5           19                                                                   
HUB SHC1023 1/2 .375-.5           3                                                                   
HUB SHC1023 1/2 .375-.5           15      

Is there anyway I can join the tables and get the vendors in  vendor1, Vendor2 and Vendor3 of table1


Output
------------  
Manu.Part no.               Description                                       Vendor1      Vendor2    Vendor3
--------------------------------------------------------------------------------------------------------------------------------
HUB SHC1023 1/2 .375-.5     CORD GRIP                                  19                  3                 15
Q#15-017-4312                RA Conveyor Extension
Q#15-017-4312 rev2          AOP50 Installation

Thanks in advance.
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.

PortletPaulfreelancerCommented:
Manu.Part no.               Description                                       Vendor1      Vendor2    Vendor3

That is not a good way to store information; for example what happens if there are 5 vendors?

However, yes it is possible,
but you need to explain why  19 = Vendor1,  3 = Vendor2,  &  15 = Vendor3
what is the logic behind that sequence?
0
RadhaKrishnaKiJayaAuthor Commented:
Hi,

Thank you for your reply. Just for example I gave the IDs like that. But I just need the vendors in the same line.
0
PortletPaulfreelancerCommented:
SELECT
      [Manu.Part no]
    , MAX(table1.Description) as Description
    , MAX(CASE WHEN rn = 1 THEN VendorID END) AS  Vendor1
    , MAX(CASE WHEN rn = 2 THEN VendorID END) AS  Vendor2
    , MAX(CASE WHEN rn = 3 THEN VendorID END) AS  Vendor3
FROM (
      SELECT
            [Manu.Part no]
          , VendorID
          , ROW_NUMBER() OVER (PARTITION BY [Manu.Part no] ORDER BY (SELECT NULL) ) AS rn
      FROM table2
     ) AS vend
INNER JOIN table1 ON vend.[Manu.Part no] = table1.[Manu.Part no]
GROUP BY
      [Manu.Part no]

Open in new window

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
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.

PortletPaulfreelancerCommented:
>>"But I just need the vendors in the same line."
please state the requirements in the question, these facts are important for choosing the best solution (which isn't what I just proposed, now I've seen that comment)

In SQL Server you can use "FOR XML PATH", but I must leave now, and someone else will pick it up.

this is an example of what I mean
SELECT
  o.intOrderID
, o.name
, o.company
, ca1.notes
, o.bonus
FROM [Order] o
    CROSS APPLY (
        SELECT
        STUFF((
              SELECT
                    ', ' + p.[strPaymentMethod] + ':$' + CAST(p.[decPaymentAmount] AS varchar(20))
               FROM [GCM_Payment] AS p
              WHERE p.intOrderID = o.intOrderID
              FOR XML PATH ('')
              )
             , 1, 1, '')
         ) ca1 (Notes)
;

Open in new window

0
RadhaKrishnaKiJayaAuthor Commented:
Thank you very much. It worked!!
0
RadhaKrishnaKiJayaAuthor Commented:
Hi Paul,

Thank you very much for your help. Actually when I tried with the sample it worked but when I am trying to include it in the actual query it is getting too confusing for me.

This is my query right now. There are 3 tables. BOM, Parts and VENDORLINECARD . I am getting ManufacturerPartNo from the Parts table. But want Vendors in the BOM table


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      

Please help me how can I add the portion of query you suggested before here.

Thanks.
0
PortletPaulfreelancerCommented:
May I say, it is almost always a bad idea to "make the question simple for us". Much better to use the real table names & colun names and sample data (excluding anything private). Ultimately you get the solution faster.

My guess is along these lines. As this is a much bigger column list I have changed the approach a little by just adjusting the vendor information first, then join to that as a derived table.
SELECT
      B.[BomMstID]
    , B.[BomID]
    , P.ManufacturerPartNo
    , v.Vendor1
    , v.Vendor2
    , v.Vendor3
    , 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 (
                        SELECT
                              ManufacturerPartNo
                            , MAX(CASE WHEN rn = 1 THEN VendorID END) AS Vendor1
                            , MAX(CASE WHEN rn = 2 THEN VendorID END) AS Vendor2
                            , MAX(CASE WHEN rn = 3 THEN VendorID END) AS Vendor3
                        FROM (
                              SELECT
                                    ManufacturerPartNo
                                  , VendorID
                                  , ROW_NUMBER() OVER (PARTITION BY ManufacturerPartNo
                                    ORDER BY (SELECT NULL)) AS rn
                              FROM VENDORLINECARD
                             ) AS vflat
                        GROUP BY ManufacturerPartNo
                      ) AS V ON v.ManufacturerPartNo = P.ManufacturerPartNo
WHERE B.CustomerID = 73
      AND B.JobNum = '15041701  '
      AND B.BomID = 20
      AND B.RevisionLvl = '1'
;

Open in new window

Please note as the question is finalized if there is much more t do you should open a new question.

why? a new question attracts any expert wanting to answer things in their favoured topics. an already answered question only attracts the persons who contributed initially (and as I'm the only contributor that means you wold be stuck with me only, and I'm not always available).
0
RadhaKrishnaKiJayaAuthor Commented:
Thank you very much for your help.
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.