Выборка с трёх таблиц

[rosty]

Выборка с трёх таблиц


Условие:
Есть 3 таблицы - product, license, version

mysql> select * from product;
+----+------+
| id | name |
+----+------+
| 1 | pr1 |
| 2 | pr2 |
| 3 | pr3 |
+----+------+

mysql> select * from license;
+----+------------+------+
| id | product_id | name |
+----+------------+------+
| 1 | 1 | l1 |
| 2 | 1 | l2 |
| 3 | 2 | ll1 |
| 4 | 3 | lll1 |
| 5 | 3 | lll2 |
| 6 | 3 | lll3 |
+----+------------+------+

mysql> select * from version;
+----+------------+------+
| id | product_id | name |
+----+------------+------+
| 1 | 1 | 4.0 |
| 2 | 1 | 4.1 |
| 3 | 1 | 4.2 |
| 4 | 1 | 4.4 |
| 5 | 2 | 1.0 |
| 6 | 2 | 1.1 |
| 7 | 3 | 0.8 |
+----+------------+------+

Т.е. - есть 3 продукта, у каждого из них есть некоторое к-во версий и некоторое число лицензий.

Задача: одним запросом вывести таблицу со следующими полями - id продукта, имя продукта, количество лицензий этого продукта и количество версий этого продукта. При вышеизложенных данных, результат должен быть таков

+----+------+---------------+---------------+
| id | name | license_count | version_count |
+----+------+---------------+---------------+
| 1 | pr1 | 2 | 4 |
| 2 | pr2 | 1 | 2 |
| 3 | pr3 | 3 | 1 |
+----+------+---------------+---------------+

Это вообще реально?!!!!
Я могу выбрать что-то одно (либо версии либо лицензии) так:

SELECT product.id, product.name,
count(license.product_id)
FROM product, license
WHERE license.product_id = product.id
GROUP BY product.id;

Но чтобы и версии и лицензии никак
Помогите, кто чем может!!!!


Дамп базы прилагается:

CREATE TABLE `license` (
`id` int(8) NOT NULL auto_increment,
`product_id` int(8) NOT NULL default '0',
`name` varchar(255) default NULL,
PRIMARY KEY (`id`,`product_id`),
KEY `product_id` (`product_id`)
) AUTO_INCREMENT=7 ;

INSERT INTO `license` VALUES (1, 1, 'l1');
INSERT INTO `license` VALUES (2, 1, 'l2');
INSERT INTO `license` VALUES (3, 2, 'll1');
INSERT INTO `license` VALUES (4, 3, 'lll1');
INSERT INTO `license` VALUES (5, 3, 'lll2');
INSERT INTO `license` VALUES (6, 3, 'lll3');

CREATE TABLE `product` (
`id` int(8) NOT NULL auto_increment,
`name` varchar(255) default NULL,
PRIMARY KEY (`id`)
) AUTO_INCREMENT=4 ;

INSERT INTO `product` VALUES (1, 'pr1');
INSERT INTO `product` VALUES (2, 'pr2');
INSERT INTO `product` VALUES (3, 'pr3');

CREATE TABLE `version` (
`id` int(8) NOT NULL auto_increment,
`product_id` int(8) default NULL,
`name` varchar(255) default NULL,
PRIMARY KEY (`id`),
KEY `product_id` (`product_id`)
) AUTO_INCREMENT=8 ;

INSERT INTO `version` VALUES (1, 1, '4.0');
INSERT INTO `version` VALUES (2, 1, '4.1');
INSERT INTO `version` VALUES (3, 1, '4.2');
INSERT INTO `version` VALUES (4, 1, '4.4');
INSERT INTO `version` VALUES (5, 2, '1.0');
INSERT INTO `version` VALUES (6, 2, '1.1');
INSERT INTO `version` VALUES (7, 3, '0.8');

 

[Apache_xp]

Re: Выборка с трёх таблиц

Есть несколько извратный вариант
select p.id, p.name,
count(if(p.id=l.product_id,1,null))/count(distinct v.id) as
license_count,
count(if(p.id=v.product_id,1,null))/count(distinct l.id) as version_count
from product as p, license as l, version as v group by p.name;
Конечно не самый лучший но все же

-~{}~ 13.02.05 15:37:

Извините за преды дущий изврат
SELECT PR.ID, PR.NAME, COUNT(DISTINCT LN.ID), COUNT(DISTINCT VR.ID) FROM PRODUCT AS PR
INNER JOIN LICENSE AS LN ON PR.ID = LN.PRODUCT_ID INNER JOIN VERSION AS VR
ON PR.ID = VR.PRODUCT_ID GROUP BY PR.NAME;