Доброго дня, помогите с решением
имеются две таблицы
| MySQL |
1
2
3
4
5
6
7
8
9
10
11
| mysql> select * from virtual_domains;
+----+----------------+--------+---------------------+----------+
| id | name | active | created | disabled |
+----+----------------+--------+---------------------+----------+
| 1 | field.ru | 1 | 2013-09-24 12:00:00 | NULL |
| 2 | ogorod.ru | 0 | 2013-10-01 11:00:00 | NULL |
| 3 | fredom.ru | 0 | 2013-10-01 15:00:00 | NULL |
| 4 | itel.ru | 1 | 2013-10-01 11:00:00 | NULL |
| 6 | mvdms.field.ru | 1 | 2013-10-01 00:00:00 | NULL |
+----+----------------+--------+---------------------+----------+
5 rows in set (0.00 sec) |
|
| MySQL |
1
2
3
4
5
6
7
8
9
10
11
12
13
| mysql> select * from virtual_users;
+----+-----------+------------+--------+-------+----------+---------------------+----------+
| id | domain_id | user | active | admin | password | created | disabled |
+----+-----------+------------+--------+-------+----------+---------------------+----------+
| 2 | 1 | admin | 1 | 0 | password | 2013-09-01 08:00:00 | NULL |
| 4 | 6 | cyrus | 1 | 1 | password | 2013-09-01 10:00:00 | NULL |
| 7 | 2 | test | 1 | 0 | password | 2013-10-01 00:00:00 | NULL |
| 8 | 1 | postmaster | 1 | 0 | password | 2013-10-01 00:00:00 | NULL |
| 9 | 3 | test | 1 | 0 | password | 2013-10-01 00:00:00 | NULL |
| 10 | 1 | megaadmin | 1 | 0 | password | 2013-09-10 00:00:00 | NULL |
| 11 | 4 | info | 1 | 0 | password | 2013-10-01 00:00:00 | NULL |
+----+-----------+------------+--------+-------+----------+---------------------+----------+
7 rows in set (0.00 sec) |
|
задача подсчитать количество пользователей у каждого домена
| MySQL |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| mysql> SELECT (virtual_users.domain_id) as count, virtual_domains.id, virtual_domains.name
from virtual_users
left join virtual_domains ON virtual_users.domain_id=virtual_domains.id;
+-------+------+----------------+
| count | id | name |
+-------+------+----------------+
| 1 | 1 | field.ru |
| 1 | 1 | field.ru |
| 1 | 1 | field.ru |
| 2 | 2 | ogorod.ru |
| 3 | 3 | fredom.ru |
| 4 | 4 | itel.ru |
| 6 | 6 | mvdms.field.ru |
+-------+------+----------------+
7 rows in set (0.00 sec) |
|
вот что то подобное
| MySQL |
1
2
3
4
5
6
7
8
9
10
| mysql> SELECT count(virtual_users.domain_id) as count, virtual_domains.id, virtual_domains.name
from virtual_users
left join virtual_domains ON virtual_users.domain_id=virtual_domains.id
where virtual_domains.id = 1;
+-------+----+----------+
| count | id | name |
+-------+----+----------+
| 3 | 1 | field.ru |
+-------+----+----------+
1 row in set (0.00 sec) |
|
но только что бы virtual_domains.id = автоматически подставлялись из таблицы virtual_domains
как устроить пока не нарыл, может массив нужен?
(
