CODEWITHSUNDEEP
mysqlsql
Efekan
Efekan

Reputation: 1505

Select from two tables and insert into one if it doesn't exist

I have three tables:

Members
 - Person_id
Sites
 - Site_id
Member_multi_site
 - Person_id
 - Site_id

What I am trying to do is insert the Person_id and Site_id into the Member_multi_site table when the same row does not exist in the Member_multi_site table.

So if in the Sites table there are 3 rows 1 2 3 And in the Members table there is 1 row 1 it should add them like so into the Member_multi_site table: 

-----------------------
| Person_id | Site_id |
-----------------------
|     1     |    1    |
|     1     |    2    |
|     1     |    3    |

I managed to get it working by manually inserting the Site_id like so:

SET @id = 0;

INSERT INTO
 Member_multi_site (
  Person_id,
  Site_id,
 )
SELECT
 Person_id,
 @id,
FROM
 Members
WHERE
 Person_id
 NOT IN (
  SELECT
   Person_id
  FROM
   Member_multi_site
  WHERE
   Site_id = @id
 )

I can't figure out how to alter this query so I dont have to manually put in the Site_id.

Upvotes: 0

Views: 41

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can cross join the tables and insert the rows if they don't already exist.

INSERT INTO
 Member_multi_site (
  Person_id,
  Site_id
 )
SELECT
 Person_id,
 Site_id
FROM
 Members
 CROSS JOIN Sites 
WHERE Person_id NOT IN (SELECT Person_id FROM Member_multi_site)
AND Site_id NOT IN (SELECT Site_id FROM Member_multi_site)

Or you can use not exists

INSERT INTO
 Member_multi_site (
  Person_id,
  Site_id
 )
SELECT
 Person_id,
 Site_id
FROM
 Members m
 CROSS JOIN Sites s
WHERE NOT EXISTS (select 1 from Member_multi_site
                  where person_id = m.person_id and site_id = s.site_id)

Upvotes: 1

Eralper
Eralper

Reputation: 6612

Please check following SQL Script on a test environment I hope it helps

insert into Member_multi_site (
    Person_id, Site_id
)
select
    t.Person_id, t.Site_id
from (
    select Person_id, Site_id from Members, Sites
) t
left join Member_multi_site m 
    on m.Person_id = t.Person_id and 
       m.Site_id = t.Site_id
where m.Site_id is null

Upvotes: 1

Related Questions