Mass associations without looping?

  • Basically I have a table T1 with people, and another table T2 with their contact mechanisms say their  phone numbers ( personal, work, etc...)
    For every T1 row, I would like to map it to 3 rows in T2 so that in T3 which is the associative table, I will have T1.ID, T2.ID.
    Is there a way to do this without looping?  Thank you very much for your time and help.

    declare @T1 table (
    PartyId int,
    Name varchar(50))
    declare @T2 table (
    ContactMechanismID int)

    declare @T3 table (
    Party int,
    ContactMechanismID int)

    Insert into @T1 (PartyID, Name)
    Select 1,'Name1' UNION ALL
    Select 2,'Name2' UNION ALL
    Select 3,'Name3'

    Insert into @T2 (ContactMechanismID)
    Select 1 UNION ALL
    Select 2 UNION ALL
    Select 3 UNION ALL
    Select 4 UNION ALL
    Select 5 UNION ALL
    Select 6 UNION ALL
    Select 7 UNION ALL
    Select 8 UNION ALL
    Select 9

    -- Results expected in T3
    --Party ID ContactMechanism
    --1            1
    --1            2
    --1            3
    --2            4
    --2            5            
    --2            6
    --3            7
    --3            8
    --3            9

  • Quick suggestion
    😎

    USE TEEST;
    GO

    declare @T1 table (
    PartyId int,
    Name varchar(50))
    declare @T2 table (
    ContactMechanismID int)

    declare @T3 table (
    Party int,
    ContactMechanismID int)

    Insert into @T1 (PartyID, Name)
    Select 1,'Name1' UNION ALL
    Select 2,'Name2' UNION ALL
    Select 3,'Name3'

    Insert into @T2 (ContactMechanismID)
    Select 1 UNION ALL
    Select 2 UNION ALL
    Select 3 UNION ALL
    Select 4 UNION ALL
    Select 5 UNION ALL
    Select 6 UNION ALL
    Select 7 UNION ALL
    Select 8 UNION ALL
    Select 9 ;

    ;WITH CONTACT_PARTY AS
    (
      SELECT
       C.ContactMechanismID
       ,(FLOOR((C.ContactMechanismID - 1) / 3) + 1) AS PartyID
      FROM  @T2 C
    )
    SELECT
      P.PartyId
     ,CP.ContactMechanismID
    FROM  @T1  P
    INNER JOIN CONTACT_PARTY CP
    ON    P.PartyId = CP.PartyID;

    Output

    PartyId    ContactMechanismID
    1    1
    1    2
    1    3
    2    4
    2    5
    2    6
    3    7
    3    8
    3    9

  • I think we need more information on what you want to achieve here.

    Eirikur's solution will give you exactly what you asked for.  But is that what you really want?

    I assume mechanisms have descriptions associated with the ID's - home phone, work phone, cell phone, pager (does anyone still have those?), email, work mail, etc.

    The results would say that Name1 has a home, work and cell phone.  Name2 has a pager, email and work email.  Name3 has 3 other things.  What does Name4 have?  

    This doesn't even consider the way you will be matching Name1 with Name1's own cell phone, and not somebody else's.  

    There has to be more to this data model to get any kind of predictable results.

  • gvoshol 73146 - Monday, April 10, 2017 5:22 AM

    I think we need more information on what you want to achieve here.

    Eirikur's solution will give you exactly what you asked for.  But is that what you really want?

    I assume mechanisms have descriptions associated with the ID's - home phone, work phone, cell phone, pager (does anyone still have those?), email, work mail, etc.

    The results would say that Name1 has a home, work and cell phone.  Name2 has a pager, email and work email.  Name3 has 3 other things.  What does Name4 have?  

    This doesn't even consider the way you will be matching Name1 with Name1's own cell phone, and not somebody else's.  

    There has to be more to this data model to get any kind of predictable results.

    Also, his solution only works if all IDs from the second table are sequential with no gaps. That's why I didn't propose that solution. But I am brain-dead from a really bad travel weekend so cannot come up with a better solution. I do feel confident there is one though.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • This method is slower but doesn't depend on sequential data, just on the PartyID being unique.

    WITH cteParties AS(
      SELECT *, ROW_NUMBER() OVER(ORDER BY PartyID) pid
      FROM @T1
    ),
    cteContactsAssignment AS(
      SELECT *, NTILE((SELECT COUNT(PartyID) Parties FROM @T1)) OVER( ORDER BY ContactMechanismID) pid
      FROM @T2
    )
    SELECT p.PartyId,
      c.ContactMechanismID
    FROM cteContactsAssignment c
    JOIN cteParties p ON c.pid = p.pid;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • This does the same as Luis's fine code, slightly more efficiently though. Hesitant in posting such a solution as the requirements are somewhat unclear.
     😎

    USE TEEST;
    GO

    declare @T1 table (
    PartyId int,
    Name varchar(50))
    declare @T2 table (
    ContactMechanismID int)

    declare @T3 table (
    Party int,
    ContactMechanismID int)

    Insert into @T1 (PartyID, Name)
    Select 1,'Name1' UNION ALL
    Select 2,'Name2' UNION ALL
    Select 3,'Name3'

    Insert into @T2 (ContactMechanismID)
    Select 1 UNION ALL
    Select 2 UNION ALL
    Select 3 UNION ALL
    Select 4 UNION ALL
    Select 5 UNION ALL
    Select 6 UNION ALL
    Select 7 UNION ALL
    Select 8 UNION ALL
    Select 9 ;

    SELECT
      C.ContactMechanismID
      ,((ROW_NUMBER() OVER
       (
        PARTITION BY @@VERSION
        ORDER BY  C.ContactMechanismID
       ) - 1) / 3) + 1 AS PartyID
    FROM @T2  C;

    The output is identical to the one before.

  • Many thanks to both Eirikur and Luis for providing a solution though the statement is unclear .  That works for what I am planning to do.  It is good to know there is great help in this forum!

    Just some more information for the inquisitive minds, the script is a data migration script.  PartyID is unique.  And I do happen to be able to  grab as many identifiers sequentially and reserve them for a script purpose.
     We are moving from a specific relational database to a universal relational database. 
    Objects that can perform actions in our domain are organizations and people all of which are parties.  Parties can be contacted via mechanisms.
    So if I have an Org row with Sales Support phone number and Help Desk Support phone Number, an OrgAddress row, a group email address for the Org in the specific database,
     I will have to get 4 mechanism IDs to specify their mechanism types, then go the specified subtypes ( Postal Address, TeleComm Number, Electronic Address) to store the finer information.

    I do have 2 options:  (1) Ensure each party gets assigned 4 sequential mechanisms and work from there 
                                   (2) Make 4 blocks of code (each block takes all orgs and one  specific mechanisms linked via their row_number())
    The objective is to make the execution time spent on data migration as short as possible for the night of the release so I am giving it a try.

    Regards

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply