Insert in a Mysql table which in turn depends on two other tables

Question

Insert in a Mysql table which in turn depends on two other tables

Navigation

#1 by (1 votes)

0

I'm trying to insert into a table called badges_members which in turn depends on two additional tables, one is called badges and the other members .

The badges_members table has the following structure:

And I am trying to insert the last badges that were discharged that are the following from the badges table:

For each user registered in the database, there must be a badge in the badges_members table, that is, if there are 6 users in the table members and 6 badges in the table badges, there must be 36 records in the table badges_members , just to mention an example. My members table has the following structure:

Currently I have 407 registered users and the new badges are 6, therefore, 2442 new registrations would have to be inserted.

I'm trying to insert them by executing the following query:

INSERT INTO badges_members (user_id, badge_id, active)
SELECT m.id, b.id, 'no' FROM badges_members bm
INNER JOIN members m ON bm.user_id=m.id
INNER JOIN badges b ON bm.badge_id=b.id
WHERE m.id NOT IN(SELECT user_id FROM badges_members)
AND b.id NOT IN(SELECT badge_id FROM badges_members);

That does not show me any error but does not insert any record either.

And if I remove the conditions to the query it shows me the following error:

The primary keys of the badges_members table are formed as follows:

mysql mariadb

asked by Guillermo Ricardo Spindola Bri 23.04.2018 в 19:45

source

1 answer

Show new BD field in cakephp view Align one button to the right with Bootstrap 4

score 1 · Answer 1

1

I did it by doing the following:

INSERT INTO badges_members (user_id, badge_id, active)
SELECT m.id, b.id, 'no' FROM members m, badges b
WHERE NOT EXISTS(SELECT 1 FROM badges_members WHERE user_id=m.id AND badge_id=b.id);

answered by 23.04.2018 в 20:48