たぬき たぬき - 1 month ago 5
MySQL Question

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
.

vkp vkp
Answer

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)
Comments