Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2014
    Posts
    2

    Unanswered: Joining Three Tables with multiple record

    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

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I started to look into this, but I gave up. This is not a normalised data model (ControlName). As far as I can say the data model is totally flawed.
    Based on this you want to make a pivotal table.

    Can you normalise your data model and give better names than Mastertable, Child1 and child2, so it's easier for us to figure out what the tables stand for? Then post your question again.
    When you do, can you also provide the CREATE TABLE and INSERT statements with the above test data, so we are able to replicate your situation?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Who designed this??? This is horrible.

    Create a view that parses the child1 table into something sensible, like:

    ID
    Child2TableID
    OrderID
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by blindman View Post
    Who designed this??? This is horrible.
    ^^^^^^^^^^
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Dec 2014
    Posts
    2

    Joining Three Tables with multiple record

    Quote Originally Posted by Wim View Post
    I started to look into this, but I gave up. This is not a normalised data model (ControlName). As far as I can say the data model is totally flawed.
    Based on this you want to make a pivotal table.

    Can you normalise your data model and give better names than Mastertable, Child1 and child2, so it's easier for us to figure out what the tables stand for? Then post your question again.
    When you do, can you also provide the CREATE TABLE and INSERT statements with the above test data, so we are able to replicate your situation?

    DB Script
    CREATE TABLE [dbo].[Child1](
    [Child1ID] [int] NOT NULL,
    [OrderID] [int] NULL,
    [ControlName] [nvarchar](max) NULL,
    CONSTRAINT [PK_Child1] PRIMARY KEY CLUSTERED
    (
    [Child1ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO
    /****** Object: Table [dbo].[Child2] Script Date: 11/11/2014 6:06:56 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Child2](
    [Child2ID] [int] NOT NULL,
    [PackageID] [int] NULL,
    [Product] [nvarchar](max) NULL,
    [Color1] [nchar](10) NULL,
    [Color2] [nchar](10) NULL,
    [Color3] [nchar](10) NULL,
    CONSTRAINT [PK_Child2] PRIMARY KEY CLUSTERED
    (
    [Child2ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO
    /****** Object: Table [dbo].[MasterTable] Script Date: 11/11/2014 6:06:56 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[MasterTable](
    [OrderID] [int] NOT NULL,
    [PackageID] [int] NULL,
    [CustomerName] [nvarchar](max) NOT NULL,
    CONSTRAINT [PK_MasterTable] PRIMARY KEY CLUSTERED
    (
    [OrderID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO
    INSERT [dbo].[Child1] ([Child1ID], [OrderID], [ControlName]) VALUES (1, 1, N'Row1Column1')
    GO
    INSERT [dbo].[Child1] ([Child1ID], [OrderID], [ControlName]) VALUES (2, 1, N'Row3Column1')
    GO
    INSERT [dbo].[Child1] ([Child1ID], [OrderID], [ControlName]) VALUES (3, 1, N'Row4Column3')
    GO
    INSERT [dbo].[Child1] ([Child1ID], [OrderID], [ControlName]) VALUES (4, 2, N'Row1Column2')
    GO
    INSERT [dbo].[Child1] ([Child1ID], [OrderID], [ControlName]) VALUES (5, 3, N'Row3Column1')
    GO
    INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (1, 1, N'Pant', N'Red ', N'Green ', N'Blue ')
    GO
    INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (2, 1, N'Shirt', N'Blue ', N'Pink ', N'Purple ')
    GO
    INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (3, 1, N'Gown', N'Blue ', N'Black ', N'Yellow ')
    GO
    INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (4, 1, N'T Shirt', N'Red ', N'Green ', N'White ')
    GO
    INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (5, 2, N'Tie', N'Red ', N'Green ', N'White ')
    GO
    INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (6, 2, N'Socks', N'Red ', N'Green ', N'White ')
    GO
    INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (7, 2, N'Bow', N'Red ', N'Green ', N'White ')
    GO
    INSERT [dbo].[MasterTable] ([OrderID], [PackageID], [CustomerName]) VALUES (1, 1, N'Abc')
    GO
    INSERT [dbo].[MasterTable] ([OrderID], [PackageID], [CustomerName]) VALUES (2, 2, N'xyz')
    GO
    USE [master]
    GO
    ALTER DATABASE [GarmentsTest] SET READ_WRITE
    GO

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I don't know what to say.

    In one way you must be an experienced SQL developer, the use of CTE's, parsing the ControlName, creating a pivotal table, ... show that you know SQL.

    On the other hand, the data model is so flawed I don't know where to start to improve it.
    The names of the tables (Mastertable, Child1 and child2) alone guarantee that you have gold at your fingers. If you are developing this for a company, that will ALLWAYS have to come back to you for maintenance, because no one else in the world will ever know what those obscure tables names stand for. Do write it down on some paper, so you too will still know in 3 or 6 months what they stand for.
    The use of ROW_NUMBER() to generate a pk for the CHild2 table is another pearl that will guarantee lifelong employment.

    I am convinced that you know SQL.
    For the design of the data model however, seek support from someone who knows how to do that. Forget everything you got so far and let that person start from scratch.
    Last edited by Wim; 12-11-14 at 09:26.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •