Joining Three Tables with multiple record

• umair2756

  Grasshopper

  Points: 11

  More actions

  December 9, 2014 at 12:28 am

  #292699

  Dear All

  we need some help. We have Three Tables in sqlserver2012

  Master Table

  OrderID PackageID CustomerName

  1 1 Abc

  2 2 Bcd

  3 1 xyz

  Child1 Table

  OrderID ControlName

  1 Row1COlumn1 (It Means Pant in Red Color is selected by user(relation with Child2 Table))

  1 Row3Column1 (It Means Gown in Blue Color is selected by user(relation with Child2 Table))

  1 Row4Column3 (It Means T Shirt in White Color is selected by user(relation with Child2 Table))

  2 Row1Column2 (It Means Tie in Green Color is selected by user(relation with Child2 Table))

  2 Row3Column1 (It Means Bow in Red Color is selected by user(relation with Child2 Table))

  Child2 Table

  PackageID Product Color1 Color2 Color3

  1 Pant Red Green Blue

  1 Shirt Blue Pink Purple

  1 Gown Blue Black Yellow

  1 T Shirt Red Green White

  2 Tie Red Green White

  2 Socks Red Green White

  2 Bow Red Green White

  We want to have result like

  OrderID PackageID CustomerName Pant Gown T Shirt Tie Bow

  1 1 ABC Red Blue White x x

  Blue

  2 2 Bcd x x x Green Red

  I have tried

  ;with mycte as (

  select ms.OrderID,ms.PackageID

  ,ms.CustomerName

  , Replace(stuff([ControlName], charindex('Column',ControlName),len(ControlName),''),'Row','') rowNum

  ,Replace(stuff([ControlName], 1, charindex('Column',ControlName)-1 ,''),'Column','') columnNum

  From child1 c inner join MasterTable ms on c.Orderid=ms.orderid)

  ,mycte1 as (

  select *, row_number() Over(Partition By PackageID Order By Child2ID) rn from child2

  )

  ,mycte2 as (

  Select m.OrderID,m.PackageID, m.CustomerName, m.ColumnNum, m1.Product

  --,m1.Color1 , m1.Color2, m1.Color3

  , Case WHEN ColumnNum= 1 Then Color1

  WHEN ColumnNum= 1 Then Color1

  WHEN ColumnNum= 2 Then Color2

  WHEN ColumnNum= 3 Then Color3 End Colors

  from mycte m

  join mycte1 m1 on m.rowNum=m1.rn and m.PackageID=m1.PackageID)

  Select OrderID,PackageID,CustomerName, ISNULL(Max(Case WHen Product='Pant' Then Colors END),'X') as 'Pant'

  , ISNULL(Max(Case WHen Product='Gown' Then Colors END),'X') as 'Gown'

  , ISNULL(Max(Case WHen Product='T Shirt' Then Colors END),'X') as 'T Shirt'

  , ISNULL(Max(Case WHen Product='Tie' Then Colors END),'X') as 'Tie'

  , ISNULL(Max(Case WHen Product='Bow' Then Colors END),'X') as 'Bow'

  FROM mycte2

  Group by OrderID,PackageID, CustomerName

  it works if we have a product in one color only. like if we have pant in red and blue then its showing just first record

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  December 9, 2014 at 1:05 am

  #1763428

  Try

  ...

  Group by OrderID,PackageID, CustomerName, Colors

  BTW, i see no Child2ID in your Child2.

  And I'd better normalize Child1 and Child2 to simplify queries. It can also really help with possible perfomance problems.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 9, 2014 at 6:45 am

  #1763502

  Child2 table has nothing to indicate which row should be chosen when say row2 is requested - you cannot rely on the order in which you think the rows sit in the table. This scheme is highly unconventional and as it stands it won't work. Have you considered taking a more conventional approach and normalising your tables?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden