mardi 5 mai 2015

insert multiple values in mysql in a nested select

I'm fairly new to sql. This might be basic. I have two tables one with groups and one with members, I want to link them up so that a third table contains id_group and id_member. The value MYGROUP is supplied during the import. I tried this:

insert ignore into member_group (id_group, id_member) 
values ( ( select id_group from group where group_name='MYGROUP' ) ,
( select id_member from member ) );

But I end up with one row in member_group containing a null value.

on it's own this yields 1 for example:

select id_group from group where group_name='MYGROUP';
+----------+
| id_group |
+----------+
|        1 |
+----------+

on it's own this yields a list of id_members

mysql> select id_member from member;
+-----------+
| id_member |
+-----------+
|       123 |
|       456 |
|       789 |

I want member_group to then look like this

+-----------+----------+
| id_group  |id_member |
+-----------+----------+
|         1 |      123 |
|         1 |      456 |
|         1 |      789 |

How can I do this (without resorting to shell scripts, for loops and sed) ?

Aucun commentaire:

Enregistrer un commentaire