К каждой записи одной таблицы-несколько записей из друго

Sandr · 8 Ноя 2007

почему при таком запросе

Код:

		$query = "SELECT w.*, group_concat(i.uid) AS i_uid, group_concat(m.uid) AS m_uid FROM " . _PREFIX . "_work AS w " .
				"INNER JOIN " . _PREFIX . "_ispolnitel AS i ON w.id = i.wid " .
				"INNER JOIN " . _PREFIX . "_manager AS m ON w.id = m.wid " .
				"GROUP BY w.id LIMIT 0,10";

я получаю
i_uid> 7,6,10,7,6,10,7,6,10
m_uid> 14,14,14,2,2,2,11,11,11

тоесть повторы
в товремя как запрос

Код:

		$query = "SELECT w.*, group_concat(i.uid) AS i_uid FROM " . _PREFIX . "_work AS w " .
				"INNER JOIN " . _PREFIX . "_ispolnitel AS i ON w.id = i.wid " .
				"GROUP BY w.id LIMIT 0,10";

я получаю i_uid> 7,6,10

AmdY · 8 Ноя 2007

если я правильно понял задание
работы
work_id | title | manager_id

исполнители
ispolnitel_id | name

работы2исполнители
id| work_id | ispolnitel_id

SELECT *
FROM работы
INNER JOIN работы2исполнители ON работы2исполнители.work_id = работы2исполнители.work_id
LEFT JOIN менеджеры ON менеджеры.manager_id = работы.manager_id

посмотри на результаты и поправь, разберёшься как работает LEFT и INNER JOIN

Gas · 8 Ноя 2007

я получаю повторы

потому что ты вообще не имеешь понятия о реляционной алгебре

может теорию подтянуть сначала?
ну а решение в лоб - добавь distinct внутрь group_concat

Sandr · 8 Ноя 2007

Автор оригинала: Gas
может теорию подтянуть сначала?

так я и писал

Автор оригинала: Sandr
отошлите чтоль к хорошей статье про такие запросы

упс

-~{}~ 08.11.07 17:27:

вот запрос

Код:

SELECT w.*, group_concat(DISTINCT i.uid) AS i_uid, 
group_concat(DISTINCT m.uid) AS m_uid, 
group_concat(DISTINCT u.name) AS manager, 
group_concat(DISTINCT ui.name) AS ispolnitel 
FROM work AS w
INNER JOIN ispolnitel AS i ON i.wid = w.id 
LEFT JOIN manager AS m ON m.wid = w.id 
INNER JOIN users AS u ON u.id_fu=m.uid 
INNER JOIN users AS ui ON ui.id_fu=i.uid 
WHERE w.status=5 AND  m.uid=2 AND  i.uid=1 GROUP BY w.id ORDER BY w.id LIMIT 5,5

всебы хорошо НО - в результате я вижу в поле ispolnitel - только того исполнителя чей id " i.uid=1" и в поле менеджера лишь соответсвующего m.uid=2

если же из WHERE убрать AND i.uid=1 - то видны все исполнители для каждой работы

Gas · 8 Ноя 2007

ну собственно а в чём вопрос?

Sandr · 9 Ноя 2007

Автор оригинала: Sandr
всебы хорошо НО - в результате я вижу в поле ispolnitel - только того исполнителя чей id " i.uid=1" и в поле менеджера лишь соответсвующего m.uid=2

как должен выглядеть запрос возвращающий ПОЛНУЮ
информацию о всех заказах

ведомых менеджером m.id=5
и выполненых исполнителем i.id = 7
для заказчика zid=12

(т.е. в том числе кто еще из менеджеров и исполнителей в этих заказах учавствовал?)

Gas · 9 Ноя 2007

Sandr
если в результате возвращается "не очень много" записей, то выносишь условия по менеджерам/исполнителям из where в having и там проверяешь строковыми функциями их наличие в результатах group_concat.

или с помощью подзапроса, в котором получаешь список работ, которые удовлетворяют нужным условиям и джойнишь полученный record set c таблицами ispolnitel,manager,users для получения остальных полных данных.

Sandr · 9 Ноя 2007

в результате такой запрос

Код:

SELECT w.*, 
group_concat(DISTINCT i.uid) AS i_uid, 
group_concat(DISTINCT m.uid) AS m_uid, 
group_concat(DISTINCT u.name) AS manager, group_concat(DISTINCT ui.name) AS ispolnitel, group_concat(DISTINCT z.name) AS zakazchik 
FROM work AS w 
INNER JOIN ispolnitel AS i ON i.wid = w.id 
LEFT JOIN manager AS m ON m.wid = w.id 
INNER JOIN users AS u ON u.id_fu=m.uid 
INNER JOIN users AS ui ON ui.id_fu=i.uid 
INNER JOIN zakazchik AS z ON z.zid=w.zid 
WHERE w.id= ANY (SELECT wid FROM manager WHERE uid = 2) 
GROUP BY w.id ORDER BY w.id LIMIT 285,15;

выполняется :
15 rows in set (6.03 sec)
и это всего на 15 строчек

-~{}~ 09.11.07 16:47:

а такой на удивление вообще почти 9 сек

Код:

SELECT w.*, 
group_concat(DISTINCT i.uid) AS i_uid, 
group_concat(DISTINCT m.uid) AS m_uid 
FROM work AS w 
INNER JOIN ispolnitel AS i ON i.wid = w.id 
LEFT JOIN manager AS m ON m.wid = w.id 
WHERE w.id= ANY (SELECT wid FROM manager WHERE uid = 2) 
GROUP BY w.id ORDER BY w.id LIMIT 285,15;

15 rows in set (8.64 sec)

Gas · 9 Ноя 2007

Ценнейший раздел документации.
Без информации о структуре таблиц и explain'ов запросов тут советовать нечего.

Sandr · 9 Ноя 2007

Код:

CREATE TABLE `ispolnitel` (
  `wid` int(11) NOT NULL default '0',
  `uid` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_general_cs;

CREATE TABLE `manager` (
  `wid` int(11) NOT NULL default '0',
  `uid` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_general_cs;

CREATE TABLE `users` (
  `id_fu` int(6) unsigned NOT NULL auto_increment,
  `u_id` tinyint(6) NOT NULL default '0',
  `name` varchar(255) NOT NULL,
  `idp` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id_fu`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_general_cs;

CREATE TABLE `work` (
  `id` int(10) NOT NULL auto_increment,
  `num` varchar(30) character set cp1251 collate cp1251_general_cs NOT NULL default '',
  `date` date NOT NULL default '0000-00-00',
  `cid` int(10) NOT NULL default '0',
  `zid` int(10) NOT NULL default '0',
  `work` longtext NOT NULL,
  `img1` varchar(255) character set cp1251 collate cp1251_general_cs default NULL,
  `status` tinyint(4) NOT NULL default '0',
  `place` text character set cp1251 collate cp1251_general_cs,
  `fin` date NOT NULL default '0000-00-00',
  `dim` varchar(255) character set cp1251 collate cp1251_general_cs NOT NULL default '0',
  `disk` int(4) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `number` (`num`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_general_cs;

CREATE TABLE `zakazchik` (
  `zid` int(10) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `shortname` varchar(5) character set cp1251 collate cp1251_general_cs NOT NULL default '',
  PRIMARY KEY  (`zid`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_general_cs;

Mr_Max · 9 Ноя 2007

Sandr
Советую почитать
5.4.3 Использование индексов в MySQL

Sandr · 9 Ноя 2007

explain последнего запроса

Код:

+----+--------------------+--------------+--------+---------------+---------+---------+------------+------+----------------------------------------------+
| id | select_type        | table        | type   | possible_keys | key     | key_len | ref        | rows | Extra                                        |
+----+--------------------+--------------+--------+---------------+---------+---------+------------+------+----------------------------------------------+
|  1 | PRIMARY            | i            | ALL    | NULL          | NULL    | NULL    | NULL       | 1412 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | w            | eq_ref | PRIMARY       | PRIMARY | 4       | nuke.i.wid |    1 |                                              |
|  1 | PRIMARY            | m            | ALL    | NULL          | NULL    | NULL    | NULL       | 1246 |                                              |
|  2 | DEPENDENT SUBQUERY | manager      | ALL    | NULL          | NULL    | NULL    | NULL       | 1246 | Using where                                  |
+----+--------------------+--------------+--------+---------------+---------+---------+------------+------+----------------------------------------------+
4 rows in set (0.00 sec)

Gas · 9 Ноя 2007

ключи на таблицы manager/ispolnitel по полям wid и uid. Я бы их вообще объеденил в одну таблицу с дополнительным полем.
на таблицу users ключ на id_fu.
Потом можешь опять сюда explain запостить.

Sandr · 9 Ноя 2007

Автор оригинала: Mr_Max
Sandr
Советую почитать
5.4.3 Использование индексов в MySQL

спасибо - как всегда самый правильный совет - "Изучайте мат часть "

главное найти нужный параграф

-~{}~ 09.11.07 17:39:

добавив индексы получил
15 rows in set (0.03 sec)

Код:

+----+--------------------+--------------+----------------+---------------+---------+---------+-----------+------+--------------------------+
| id | select_type        | table        | type           | possible_keys | key     | key_len | ref       | rows | Extra                    |
+----+--------------------+--------------+----------------+---------------+---------+---------+-----------+------+--------------------------+
|  1 | PRIMARY            | w            | index          | PRIMARY       | PRIMARY | 4       | NULL      | 1246 | Using where              |
|  1 | PRIMARY            | m            | ref            | wid           | wid     | 4       | nuke.w.id |    1 |                          |
|  1 | PRIMARY            | i            | ref            | wid           | wid     | 4       | nuke.w.id |    1 |                          |
|  2 | DEPENDENT SUBQUERY | fora_manager | index_subquery | users,wid     | wid     | 4       | func      |    1 | Using index; Using where |
+----+--------------------+--------------+----------------+---------------+---------+---------+-----------+------+--------------------------+
4 rows in set (0.00 sec)

-~{}~ 13.11.07 16:15:

еще один вопрос
нужно выбрать работы где конкретный исполнитель и конкретный менеджер
т.е. подзапрос

Код:

SELECT w.*, 
group_concat(DISTINCT i.uid) AS i_uid, 
group_concat(DISTINCT u.name) AS ispolnitel 
FROM work AS w 
INNER JOIN ispolnitel AS i ON i.wid = w.id 
INNER JOIN users AS u ON u.id_fu=i.uid 
WHERE w.id= ANY (SELECT wid FROM ispolnitel WHERE (uid = 2 AND pid = 2) OR (uid = 18 AND pid = 1) ) 
GROUP BY w.id ORDER BY w.id LIMIT 15,15

вернет мне ВСЕ работы с менеджером - 2 и все работы с исполнителем - 18

а как получить только пересекающиеся работы?

Gas · 13 Ноя 2007

Код:

...
INNER JOIN ispolnitel AS i1 ON i1.wid = w.id
INNER JOIN ispolnitel AS i2 ON i2.wid = w.id
INNER JOIN users AS u ON (u.id_fu=i1.uid OR u.id_fu=i2.uid)
WHERE i1.uid = 2 AND i1.pid = 2 AND i2.uid = 18 AND i2.pid = 1
GROUP BY w.id ORDER BY w.id LIMIT 15,15

?

p.s. аццкий пост! (666 - может положить сервер

Sandr · 13 Ноя 2007

Код:

WHERE i1.uid = 2 AND i1.pid = 2 AND i2.uid = 18 AND i2.pid = 1

и снова наши бараны - я получаю имена только запрошенных исполнителей/менеджеров
а если вработе учавствовало больше людей - результат не отображает

Gas · 13 Ноя 2007

тогда измени свой подзапрос так:

ANY (SELECT wid FROM ispolnitel AS i1, ispolnitel AS i2 WHERE i1.wid=i2.wid AND i1.uid = 2 AND i1.pid = 2 AND i2.uid = 18 AND i2.pid = 1)

Sandr · 14 Ноя 2007

небольшая поправочка

ANY (SELECT i1.wid FROM ispolnitel AS i1, ispolnitel AS i2 WHERE i1.wid=i2.wid AND i1.uid = 2 AND i1.pid = 2 AND i2.uid = 18 AND i2.pid = 1)

ато - ERROR 1052 (23000): Column 'wid' in field list is ambiguous

-~{}~ 14.11.07 17:29:

но по любому спасибо

К каждой записи одной таблицы-несколько записей из друго

Sandr

Новичок

AmdY

Пью пиво

Gas

может по одной?

Sandr

Новичок

Gas

может по одной?

Sandr

Новичок

Gas

может по одной?

Sandr

Новичок

Gas

может по одной?

Sandr

Новичок

Mr_Max

Первый класс. Зимние каникулы ^_^

Sandr

Новичок

Gas

может по одной?

Sandr

Новичок

Gas

может по одной?

Sandr

Новичок

Gas

может по одной?

Sandr

Новичок