Joining Three Tables with multiple record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Joining Three Tables with multiple record

Author

Topic

umair2756
Starting Member

1 Post

Posted - 2014-12-09 : 06:13:00

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

Muhammad Umair

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-09 : 09:43:44

dunno how your query works at all. I get the error message:

quote:

Msg 207, Level 16, State 1, Line 50
Invalid column name 'Child2ID'.

Indeed there is no such column!

Here it is reformatted:


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;

Please use poorsql.com or APEX Reformat or something like that when posting queries and enclose them in

...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources