· 8 years ago · May 18, 2018, 04:14 PM
1CREATE TABLE IF NOT EXISTS `users` (
2 `id` int(6) unsigned NOT NULL,
3 `name` varchar(16) NOT NULL,
4 PRIMARY KEY (`id`)
5) DEFAULT CHARSET=utf8;
6
7CREATE TABLE IF NOT EXISTS `user_group` (
8 `user_id` int(6) unsigned NOT NULL,
9 `group_id` int(6) unsigned NOT NULL,
10 PRIMARY KEY (`user_id`, `group_id`)
11) DEFAULT CHARSET=utf8;
12
13CREATE TABLE IF NOT EXISTS `groups` (
14 `id` int(6) unsigned NOT NULL,
15 `name` varchar(16) NOT NULL,
16 PRIMARY KEY (`id`)
17) DEFAULT CHARSET=utf8;
18
19INSERT INTO `users` (`id`, `name`) VALUES
20 ('1', 'Michael'),
21 ('2', 'Sarah'),
22 ('3', 'Steven'),
23 ('4', 'Jane');
24
25INSERT INTO `groups` (`id`, `name`) VALUES
26 ('1', 'M Names'),
27 ('2', 'S Names'),
28 ('3', 'J Names'),
29 ('4', 'Men'),
30 ('5', 'Women');
31
32INSERT INTO `user_group` (`user_id`, `group_id`) VALUES
33 ('1', '1'),
34 ('1', '4'),
35 ('2', '2'),
36 ('2', '5'),
37 ('3', '2'),
38 ('3', '4'),
39 ('4', '3'),
40 ('4', '5');
41
42SELECT * FROM `user_group`
43WHERE `user_id` in ('2', '3');