Имеются две следующие таблицы:

CREATE TABLE `predmet` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(16) NOT NULL default '',
PRIMARY KEY (`id`)
);

INSERT INTO `predmet` VALUES (1, 'Первый');
INSERT INTO `predmet` VALUES (2, 'Второй');

CREATE TABLE `color` (
`id` int(11) NOT NULL auto_increment,
`predmet` int(11) NOT NULL default '0',
`name` varchar(16) NOT NULL default '',
PRIMARY KEY (`id`)
);

INSERT INTO `color` VALUES (1, 1, 'синий');
INSERT INTO `color` VALUES (2, 1, 'красный');

---

Смысл в том, что есть предметы, а есть цвета. Причём, не у каждого предмета есть цвет, а если есть, то цветов может быть несколько.
В данном примере у предмета "Первый" 2 цвета, у предмета "Второй" ни одного.

Мне нужно получить список вида:

id предмета; имя предмета; кол-во цветов у предмета

Если я пишу
SELECT predmet.id,predmet.name,COUNT(color.id) FROM predmet,color WHERE color.predmet=predmet.id GROUP BY predmet.id

то получаю только:

id name COUNT(color.id)
1 Первый 2

потому что у второго предмета нет цветов в таблице color, и не выполняется условие color.predmet=predmet.id

Как правильно написать запрос, чтобы он выводил такое:
id name COUNT(color.id)
1 Первый 2
2 Второй 0

То есть, если цвета есть - тогда их кол-во, иначе - ноль.

Я понимаю, можно каждому предмету назначить цвет, дефолтный, и считать кол-во цветов - 1, но в моей задаче это неудобно. Да и вообще интересно узнать ответ, в принципе.

Welly Sly_GЕсли я пишу
SELECT predmet.id,predmet.name,COUNT(color.id) FROM predmet,color WHERE color.predmet=predmet.id GROUP BY predmet.id

Не в качестве наезда, но вот интересно, откуда такой стиль объединения таблиц, через условие, а не явное указание JOIN? Причем такие вопросы задаются с пугающей регулярностью. Ничего не понимаю, это так в ВУЗах учат? Или виноваты учебники по SQL, недочитанные до середины?

Это необходимость что-то срочно делать по работе без достаточного времени для того, чтобы учебники читать.
Это дело хорошее, только где взять время на него.

А в вузе меня SQL не учили, специальность другая была.

sanek842 Sly_GДа и вообще интересно узнать ответ, в принципе.
Ну раз впринципе интересно, что ж, лови ответ :)

SELECT predmet.id,predmet.name,COUNT(color.id) FROM predmet LEFT OUTER JOIN color ON (color.predmet=predmet.id) GROUP BY predmet.id;

Спасибо большое!

sanek842Ну раз впринципе интересно, что ж, лови ответ :)

SELECT predmet.id,predmet.name,COUNT(color.id) FROM predmet LEFT OUTER JOIN color ON (color.predmet=predmet.id) GROUP BY predmet.id;

Работает, но как-то заметно долго. 1000 предметов и 500 цветов, запрос секунд 5 обрабатывает.

Один вариант - это в таблице predmet сделать отдельный столбец для количества цветов предмета и его обновлять.

А второй вариант - индексы сделать. Какие индексы в данном случае правильно сделать?

Welly Sly_GОдин вариант - это в таблице predmet сделать отдельный столбец для количества цветов предмета и его обновлять.

Можно, но если не использовать транзакции, то могут быть нарушения логической целостности данных - в этом поле может оказаться одно значение, а реальное количество записей в color будет другое.


Да, этого и боюсь.


А второй вариант - индексы сделать. Какие индексы в данном случае правильно сделать?
ИМХО, проще, и работает на всех видах таблиц
ALTER TABLE color ADD INDEX fk_predmet_idx(predmet);[/quot]

Ага, то есть просто по предметам..
Ух ты, в десятки раз ускорился. Круто.

Не, это не дело - надо книгу читать умную Ж)
Можете посоветовать книжку хорошую, на русском, по этим вопросам? Не совсем mysql с нуля изучать,- больше про оптимизацию, хитрые запросы...

Welly
По MySQL:
Поль Дюбуа. MySQL. Second Edition

По SQL (пусть вас слово "Введение" не обманывает, книга серьезная):
Мартин Грабер. Введение в SQL



Купил вчера Дюбуа, "mysql Сборник рецептов". Толково написано.
Постараюсь найти указанную вами книжку.
Спасибо!