I have 3 tables, staging, positions_mapping, and target.

staging table

positions_mapping table

And on the target table I intend to insert those rows from staging like this:

At first I do this by using JOINS:

INSERT INTO target(position_id)
SELECT positions_mapping.id FROM staging
LEFT JOIN positions_mapping ON positions_mapping.position_name = staging.pos_name 

But that works for an exact match only. Different names for Agile Scrum Master came and I still need to insert those as "Agile Scrum Master" regardless if it's an exact match or not.

I'm thinking of having conditional statements here? I'm not so sure where to place them.

CodePudding user response：

If I understand correctly, you can try to use CROSS JOIN

INSERT INTO target(position_id)
SELECT positions_mapping.id 
FROM staging
CROSS JOIN positions_mapping 
WHERE position_name = 'Agile Scrum Master'

CodePudding user response：

Wizh a full zexz search you could fond two of the rows but the first is to generic to find it.

But as you can't ppu any column of another table you need a dynamic approach

CREATE TABLE staging (
  `id` INTEGER,
  `pos_name` VARCHAR(18),
   FULLTEXT KEY (pos_name )
);

INSERT INTO staging
  (`id`, `pos_name`)
VALUES
  ('4', 'SM'),
  ('5', 'Agile Scrum Master'),
  ('6', 'Scrum Master');

CREATE TABLE positions_mapping (
  `id` INTEGER,
  `position_name` VARCHAR(18)
);

INSERT INTO positions_mapping
  (`id`, `position_name`)
VALUES
  ('1', 'Agile Scrum Master');

PREPARE stat FROM "SELECT s.`id`,pm.`id` 
FROM staging s JOIN positions_mapping pm ON MATCH (s.`pos_name` ) AGAINST (?) > 0";
SET @c_val = (SELECT `position_name` FROM positions_mapping WHERE  `position_name` = 'Agile Scrum Master');
EXECUTE stat USING @c_val;

✓

✓

id | id
-: | -:
 5 |  1
 6 |  1

db<>fiddle here