So here is the problem :
I have a table "Members" with members and their attributes (name, birthday, mail, etc.) These members may belong to groups (let's say there are 3 groups), from none to all of them. And these groups are referenced in a table ("Groups") so I can add/delete/modify them as I want.
SET() doesn't seem to be a solution, it isn't compatible with foreign keys / reference table.
So at first, I was thinking of doing a TINYINT() column, which I use like SET() : 111 (7) for all groups, 000 (0) for none, 001 (1) for the 1st group , 010 (2) for the 2nd, etc. But since the names are quite complex, it's confusing, and not much more compatible with foreign keys.
I read that I should do a 3rd table "Members-Groups" with memberID and groupID to join both of my two tables, but I don't clearly understand how it work.
What I understand is that I will have a table with IDs of members and groups like this :
+----------+---------+
| memberID | groupID |
+----------+---------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 3 |
| 3 | 2 |
+----------+---------+
and combined with junction I can retrieve what I want. Is it right ? Otherwise can someone explain me how i should do ?
I precise that I'd like to have as final result (after sql request + php script) a member, his attributes and the groups he belongs to in a single row (as with SET()), even members that doesn't belong to any group.
Assuming
drop table if exists mg;
drop table if exists m;
create table m (id int primary key, name varchar(3));
insert into m values
(1,'abc'),
(2,'def'),
(3,'ghi');
drop table if exists g;
create table g(id int primary key ,name varchar(3));
insert into g values
(1,'aaa'),
(2,'bbb'),
(3,'ccc');
create table mg
(memid int,grid int,
index fmid(memid,grid) ,
foreign key (memid) references m(id) on delete cascade,
foreign key (grid) references g(id) on delete cascade
);
insert into mg values
(1,1),(1,2),(1,3),
(2,1),(2,3);
You could join the 3 tables and produce the results using group_concat or conditional aggregation.
MariaDB [sandbox]> select m.id,m.name, group_concat(g.name) groups
-> from m
-> join mg on mg.memid = m.id
-> join g on mg.grid = g.id
-> group by m.id,m.name;
+----+------+-------------+
| id | name | groups |
+----+------+-------------+
| 1 | abc | aaa,bbb,ccc |
| 2 | def | aaa,ccc |
+----+------+-------------+
2 rows in set (0.00 sec)
MariaDB [sandbox]>
MariaDB [sandbox]> select m.id,m.name,
-> max(case when g.id = 1 then g.name else '' end) as group1,
-> max(case when g.id = 2 then g.name else '' end) as group2,
-> max(case when g.id = 3 then g.name else '' end) as group3
-> from m
-> join mg on mg.memid = m.id
-> join g on mg.grid = g.id
-> group by m.id,m.name;
+----+------+--------+--------+--------+
| id | name | group1 | group2 | group3 |
+----+------+--------+--------+--------+
| 1 | abc | aaa | bbb | ccc |
| 2 | def | aaa | | ccc |
+----+------+--------+--------+--------+
2 rows in set (0.00 sec)
If you want members who don't belong to any group change the joins to left joins.
ariaDB [sandbox]> select m.id,m.name, group_concat(g.name) groups
-> from m
-> left join mg on mg.memid = m.id
-> left join g on mg.grid = g.id
-> group by m.id,m.name;
+----+------+-------------+
| id | name | groups |
+----+------+-------------+
| 1 | abc | aaa,bbb,ccc |
| 2 | def | aaa,ccc |
| 3 | ghi | NULL |
+----+------+-------------+
3 rows in set (0.00 sec)
MariaDB [sandbox]>
MariaDB [sandbox]> select m.id,m.name,
-> max(case when g.id = 1 then g.name else '' end) as group1,
-> max(case when g.id = 2 then g.name else '' end) as group2,
-> max(case when g.id = 3 then g.name else '' end) as group3
-> from m
-> left join mg on mg.memid = m.id
-> left join g on mg.grid = g.id
-> group by m.id,m.name;
+----+------+--------+--------+--------+
| id | name | group1 | group2 | group3 |
+----+------+--------+--------+--------+
| 1 | abc | aaa | bbb | ccc |
| 2 | def | aaa | | ccc |
| 3 | ghi | | | |
+----+------+--------+--------+--------+
3 rows in set (0.00 sec)