1

I've got the following tables:

users:

mysql [test]> SHOW CREATE TABLE users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `product` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

groups:

mysql [test]> SHOW CREATE TABLE `groups`\G
*************************** 1. row ***************************
       Table: groups
Create Table: CREATE TABLE groups (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

products:

mysql [test]> SHOW CREATE TABLE products\G
*************************** 1. row ***************************
       Table: products
Create Table: CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `product_id` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

I would like to know how many groups are active for a user:

mysql [test]> SELECT u.name, SUM(g.active) AS active FROM users u LEFT JOIN groups g ON (u.id = g.user_id) WHERE u.name IN ('user01', 'user02') GROUP BY u.name ORDER BY u.name;
+--------+--------+
| name   | active |
+--------+--------+
| user01 |      2 |
| user02 |      0 |
+--------+--------+
2 rows in set (0.00 sec)

I also need a list of products per user but then active column show incorrect values due to JOIN:

mysql [test]> SELECT u.name, SUM(g.active) AS active, GROUP_CONCAT(DISTINCT p.name ORDER BY p.name SEPARATOR ',') AS products FROM users u LEFT JOIN groups g ON (u.id = g.user_id) LEFT JOIN products p ON (u.product = p.product_id) WHERE u.name IN ('user01', 'user02') GROUP BY u.name ORDER BY u.name;
+--------+--------+-------------------------------+
| name   | active | products                      |
+--------+--------+-------------------------------+
| user01 |      6 | product01,product02,product03 |
| user02 |      0 | product11,product12,product13 |
+--------+--------+-------------------------------+
2 rows in set (0.00 sec)

So the expected results would be:

+--------+--------+-------------------------------+
| name   | active | products                      |
+--------+--------+-------------------------------+
| user01 |      2 | product01,product02,product03 |
| user02 |      0 | product11,product12,product13 |
+--------+--------+-------------------------------+
2 rows in set (0.00 sec)

Based on the following records:

mysql [test]> SELECT * FROM users;
+----+--------+---------+
| id | name   | product |
+----+--------+---------+
|  1 | user01 |       1 |
|  2 | user02 |       2 |
+----+--------+---------+
2 rows in set (0.00 sec)

mysql [test]> SELECT * FROM groups;
+----+---------+---------+--------+
| id | user_id | name    | active |
+----+---------+---------+--------+
|  1 |       1 | group01 |      0 |
|  2 |       1 | group02 |      1 |
|  3 |       1 | group03 |      1 |
|  4 |       1 | group04 |      0 |
|  5 |       2 | group01 |      0 |
|  6 |       2 | group02 |      0 |
+----+---------+---------+--------+
6 rows in set (0.00 sec)
mysql [test]> SELECT * FROM products;
+----+-----------+------------+
| id | name      | product_id |
+----+-----------+------------+
|  1 | product01 |          1 |
|  2 | product02 |          1 |
|  3 | product03 |          1 |
|  4 | product11 |          2 |
|  5 | product12 |          2 |
|  6 | product13 |          2 |
+----+-----------+------------+
6 rows in set (0.00 sec)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.