Inserting into a join table if value does not already exist

Question

I have 2 tables - user, region and a join/connecting table that is used to join both user and region.

I need to insert into the join table all the region values that the user does not already have and i am unsure how to go about this.

I have attempted this numerous ways but i am not entirely sure how to only place the values that do already exist within the table into the join table. Does anyone have any ideas or suggestions?

SELECT 
    CONVERT( CONCAT('INSERT INTO user_region VALUES(',
        user.id,
        ',',
        reg.id,
        ');') USING UTF8)
FROM
   user user
      JOIN
        user_region user_reg ON user_reg.id = user.id
      JOIN
        region reg ON reg.id = user_reg.id
WHERE 
   (user.email_address LIKE '%gmail%'
    OR user.email_address LIKE '%hotmail%');


 User Table                User Region             Region
 -----------               -----------             ------
 1                          1        2             1
 2                          3        2             2
 3                          3        4             3
 4                          4        3             4

Do the LEFT JOIN and in WHERE condition have reg.ID IS NULL .. that way you will get only regions that user doesn't have ... if you show us some data we can help you even more, but this is just idea how i would go with it — Veljko89, Aug 11 '16 at 11:53
Thanks for the reply. I am not entirely sure i follow - so i must use left joins instead of just joins and also change my where condition to check to see of the reg.is is null? — Perfect_Comment, Aug 11 '16 at 12:01
Yes. @Veljko89 is right. He has already explained how it will work. — The Shooter, Aug 11 '16 at 12:07
Does this mean i must rewrite the query to accommodate the regid= null condition? Whenever i try to place that into the where clause it throws an error. — Perfect_Comment, Aug 11 '16 at 13:15

score 0 · Answer 1 · answered Aug 11 '16 at 12:07

Kind of

INSERT INTO user_region (userID, regionID)
SELECT u.userID, r.regionID
FROM
(SELECT DISTINCT userId 
FROM user 
WHERE user.email_address LIKE '%gmail%'
    OR user.email_address LIKE '%hotmail%') u
JOIN region r ON NOT EXISTS (
     SELECT 1 
     FROM user_region ur
     WHERE ur.userID = u.userID AND ur.regionID = r.regionID )

Inserting into a join table if value does not already exist

1 Answers1