locked
Many to many relationship RRS feed

  • Question

  • Hi Guys,

    I have two tables at the moment.
    tblCGs table and #tblCs.
    The tblCGs is a lookup table to get GroupMasterCustomer key with associated clients in tblCs table. However, the tblCGs  keys are in range, like GroupFromCustKey to  CG.GroupToCustKey. I don't want the range keys but, individual row per record or move all the associated client into individual table.
    Currently, I queried the tables using the script below to get the parent and the children which are one direction only.
    --- Current Queries
    SELECT *
    FROM #tblCGs AS CG
    INNER JOIN #tblCs AS C
    ON C.CustomerKey >= CG.GroupFromCustKey
    AND C.CustomerKey <= CG.GroupToCustKey
    How can I restructure the table to accommodate to look like this
    ID ClientID1 ClientID2 ReasonTypeID
    1 201 301 1
    2 201 302 1
    3 201 303 1
    4 303 401 1
    5 601 701 3
    and not customerkey from MIND227 - MIND227. Please see the sample data below. 
    IF OBJECT_ID('tempdb..#tblCGs') IS NOT NULL DROP TABLE #tblCGs
    IF OBJECT_ID('tempdb..#tblCs') IS NOT NULL DROP TABLE #tblCs

    --Create ClientGroups table.
    CREATE TABLE #tblCGs
    (
    [GroupID] int NOT NULL, ---- IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [GroupMasterCustKey] varchar(100) NOT NULL, ---[MasterCAN]
    [GroupFromCustKey] varchar(100) NOT NULL, --- CONSTRAINT [DF_tblCGs_GroupFromCustKey] DEFAULT (' '),
    [GroupToCustKey] varchar(100) NOT NULL -- CONSTRAINT [DF_tblCGs_GroupToCustKey] DEFAULT (' '),
    )
    -- Populate the table with values.
    INSERT INTO #tblCGs VALUES
    (988, N'MIN036', N'MIND227', N'MIND229')
    , (668, N'08035635', N'31036422', N'31036422')
    ,(669, N'08035635', N'31035623', N'31035623')
    -- Create Customer table.
    CREATE TABLE #tblCs ---Customers
    ([CustomerID] int not null ------IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    ,[CustomerKey] varchar(100) NOT NULL --- [ChildCAN]
    ,[CompanyName] varchar(300) NULL
    ,[AssociatedClient] varchar(20)
    ,[CreatedOn] Datetime
    )
    --Populate the table with values.
    INSERT INTO #tblCs VALUES
    (72894, N'MIND227', N'MIND Mind in Haringey', N'MIN036','2011-11-16 00:00:00.000')
    ,(93469, N'MIND228', N'Caerphilly Borough Mind', N'MIN036','2014-03-08 00:00:00.000')
    ,(98199, N'MIND229', N'Brecon and District Mind', N'MIN036,BDC004,POW044','2014-11-26 00:00:00.000')
    ,(61705, N'31036422', N'Revive', N'Null','2010-04-01 09:13:10.550')
    ,(61729, N'31035623', N'Jysk Ltd', N'Null','2010-04-01 12:56:16.837')
    Saturday, December 23, 2017 9:06 AM

Answers