cte? self join, something else?

  • Hi, I am struggling with a merge statement. I have a staging table with two names in it. I've simplified it for this but its

    Create Table stg_partner_names

    ( t1_partner1 varchar(100)

    ,t2_partner2 varchar(100)

    ,t3_partner3 varchar(100)

    , risk_score decimal (10,2))

    insert into stg_partner_names (t1_partner1,t2_partner2,t3_partner3) VALUES( 'Bob','Rita', 'Sue')

    so the hierarchy is t3 reports to t2 and t2 reports to t1

    I want to populate a partner relationship table using these values to lookup the id for each of the names

    create table partner_relationship (

    id integer not null IDENTITY(1,1) PRIMARY KEY,

    --partner_relationship_type_id integer null,

    partner_from integer null,

    partner_to integer null)

    The ids for each partner are here, so I want to return the ids for each partner using the partner_name:

    create table partner (

    id integer not null IDENTITY(1,1) PRIMARY KEY,

    uid integer null,

    partner_name varchar(255) null)

    insert into partner (partner_name) VALUES('Bob')

    insert into partner (partner_name) VALUES('Rita')

    insert into partner (partner_name) VALUES('Sue')

    I would expect to see in partner_relationship once Ive inserted

    id, partner_from partner_to

    1 3 2

    2 2 1

    Do I need self joins , cte ? Would l it be better to do it as two separate inserts for each 'tier'?

    Thanks for any help in advance

  • Here is one way. It assumes that none of the partner_name columns are NULL – some JOIN tweaking may be required if they are.

    DROP TABLE IF EXISTS #stg_partner_names;

    CREATE TABLE #stg_partner_names
    (
    t1_partner1 VARCHAR(100)
    ,t2_partner2 VARCHAR(100)
    ,t3_partner3 VARCHAR(100)
    ,risk_score DECIMAL(10, 2)
    );

    INSERT #stg_partner_names
    (
    t1_partner1
    ,t2_partner2
    ,t3_partner3
    )
    VALUES
    ('Bob', 'Rita', 'Sue');

    DROP TABLE IF EXISTS #partner;

    CREATE TABLE #partner
    (
    id INTEGER NOT NULL IDENTITY(1, 1) PRIMARY KEY
    ,uid INTEGER NULL
    ,partner_name VARCHAR(255) NULL
    );

    INSERT #partner
    (
    partner_name
    )
    VALUES
    ('Bob')
    ,('Rita')
    ,('Sue');

    DROP TABLE IF EXISTS #partner_relationship;

    CREATE TABLE #partner_relationship
    (
    id INTEGER NOT NULL IDENTITY(1, 1) PRIMARY KEY
    ,partner_from INTEGER NULL
    ,partner_to INTEGER NULL
    );

    INSERT #partner_relationship
    (
    partner_from
    ,partner_to
    )
    SELECT partner_from = p3.id
    ,partner_to = p2.id
    FROM #stg_partner_names spn
    JOIN #partner p3
    ON p3.partner_name = spn.t3_partner3
    JOIN #partner p2
    ON p2.partner_name = spn.t2_partner2
    UNION ALL
    SELECT partner_from = p2.id
    ,partner_to = p1.id
    FROM #stg_partner_names spn
    JOIN #partner p2
    ON p2.partner_name = spn.t2_partner2
    JOIN #partner p1
    ON p1.partner_name = spn.t1_partner1;

    SELECT *
    FROM #partner_relationship pr;

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

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

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