cte? self join, something else?

• TexasJoe

  Valued Member

  Points: 62

  More actions

  November 2, 2022 at 8:49 am

  #4107657

  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

• Phil Parkin

  SSC Guru

  Points: 246977

  More actions

  November 2, 2022 at 11:30 am

  #4107706

  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

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• November 7, 2022 at 7:44 am

  This was removed by the editor as SPAM

• November 8, 2022 at 10:01 am

  This was removed by the editor as SPAM

• November 9, 2022 at 7:44 am

  This was removed by the editor as SPAM

• November 14, 2022 at 10:00 am

  This was removed by the editor as SPAM