Есть база, условно с двумя таблицами - продукция и показатели. Нужно вывести список показателей так, чтобы в каждом был ещё список продукции. Сейчас я это вывожу двумя отдельными запросами в PHP, из-за этого становится невозможным сортировка через ORDER BY. Можно это как-то вывести одним запросом в базу? Пробовал так, выдаёт ошибку "Subquery returns more than 1 row": PHP: SELECT p.`id` AS `p_id`, (SELECT `id` FROM `products` WHERE `pokazatel`=`p_id`) AS `product_ids` FROM `pokazateli` AS `p` --- Добавлено --- Даже не спрашивайте зачем заказчику нужно сортировать по столбцу "продукция", когда там несколько значений. Сам этого не понимаю. Единственное что пока придумал для сортировки по этому столбцу это вытаскивать вообще все значения из базы и через PHP делать array_multisort.
Нифига не получается. Я переделал структуру немного, теперь у меня в таблице показателей есть столбец `products`, где я храню id продукции через запятую: 806,807. Решил сделать так, чтобы эту строку можно было прямо в запросе пихать в IN(). Пробовал с JOIN: PHP: SELECT *, p.`id` AS `id` FROM `pokazateli` AS `p` LEFT JOIN `products` ON (`products`.`id` IN (p.`products`)) Выдаёт: Код (Text): Unknown column 'p.products' in 'on clause' Пробовал как в первом посте: PHP: SELECT *, p.`id` AS `id`, (SELECT `name_product` FROM `products` WHERE `id` IN(p.`products`)) AS `all_products` FROM `pokazateli` AS `p` В итоге подзапрос возвращает в "all_products" название только одной продукции. Даже ошибки больше нет "Subquery returns more than 1 row". Как мне тут склеить название всех продукций в одну строку? Желательно через разделитель.
А IN в LEFT JOIN ты сам придумал? Вообще то он работает так: PHP: LEFT JOIN `products` ON `products`.`id` = p.`products`
@Artur_hopf ага, сам)) LEFT JOIN заработал, если не обзывать таблицу показателей AS `p`. Но выдаёт всё-равно не то что нужно - выдаёт значение только одной продукции. А мне нужно в подзапросе получить названия всех продуктов по списку айдишников и склеить их названия. Странно что вот это тоже выдаёт одно название. Тут в IN попадает 2 айдишника через запятую. PHP: SELECT `pokazateli`.`products`, (SELECT `name_product` FROM `products` WHERE `id` IN(`pokazateli`.`products`)) AS `all_products` FROM `pokazateli` Вот если я в IN руками вписываю значения, например IN(1,2), то выдаёт ошибку "Subquery returns more than 1 row", с которой по идеи уже что-то можно сделать с CONCAT.
Окей, я кажется понял как засунуть в подзапрос айдишники руками - не суть. В этом подзапросе можно как-то склеить результаты в одну строку? PHP: (SELECT `name_product` FROM `products` WHERE `id` IN(1,2)) AS `all_products` Мне надо в `all_products` получить строку 'Бабушкины носкиСвитера с катушками'. Мне это нужно для возможности сортировки запроса по `all_products`.
ну допустим получить ты их получил, а склеивать кто будет Пушкин? дословно "подзапрос вернул больше одной строки", тебе из этого надо сделать одну строку читай про GROUP_CONCAT
Ууу каеф, заработало. Всем спасибо) PHP: SELECT `pokazateli`.`products`, (SELECT GROUP_CONCAT(`name_product`) FROM `products` WHERE `id` IN(1,2)) AS `all_products` FROM `pokazateli` --- Добавлено --- Я только не понял почему в IN не скармливается значение из другой таблицы. Там же строка, где лежат айдишники через запятую и по идеи должно так работать: Код (Text): SELECT `pokazateli`.`products`, (SELECT GROUP_CONCAT(`name_product`) FROM `products` WHERE `id` IN(`pokazateli`.`products`)) AS `all_products` FROM `pokazateli` Но в итоге в итоге `all_products` попадает только одно значение и вообще оно не от этого показателя.
потому, что айдишнеков через запятую там не должно быть, ибо это нарушение первого закона нормализации, кое грозит невозможностью работать с данными на уровне СУРБД, что собственно и подтверждается этим примером.
@Valick а это решаемо? Можно в этот подзапрос как-то скормить значение столбца `products` из таблицы `pokazateli`? --- Добавлено --- Я импортирую базу из нереляционной IBM Lotus в реляционную MySQL, там со структурой вообще ужас, по другому тут не сделать.
для начала необходимо грамотно создать архитектуру БД, потом становится всё решаемо --- Добавлено --- не надо ля-ля
У меня изначально так и было, но я почему-то решил, что через запятую проще. Окей, если я перегоню это в отдельную таблицу связей, то что это даст? Допустим, будет: pokazateli - id - name products - id - name relations - id - pokazatel_id - product_id Я уже готов закинуть 500р тому кто решит эту задачу.) За обучение, так сказать. Тут нужно одним запросом вывести всё из таблицы pokazateli, а названия продукции вывести в одну строчку, если они есть. Меня пугает вот это "если они есть". --- Добавлено --- Вот вам дамп базы. Может кто-то сделает, с меня 500р тогда. =\ Код (Text): -- phpMyAdmin SQL Dump -- version 4.8.5 -- https://www.phpmyadmin.net/ -- -- Хост: localhost -- Время создания: Дек 05 2019 г., 15:28 -- Версия сервера: 5.7.25 -- Версия PHP: 7.0.8 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET AUTOCOMMIT = 0; START TRANSACTION; SET time_zone = "+00:00"; -- -- База данных: `testy-test` -- CREATE DATABASE IF NOT EXISTS `testy-test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; USE `testy-test`; -- -------------------------------------------------------- -- -- Структура таблицы `pokazateli` -- CREATE TABLE `pokazateli` ( `id` int(8) NOT NULL, `name_pokazatel` text NOT NULL COMMENT 'id названия показателя' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Показатели'; -- -- Дамп данных таблицы `pokazateli` -- INSERT INTO `pokazateli` (`id`, `name_pokazatel`) VALUES (1, 'Ворсистость'), (2, 'Пустой'); -- -------------------------------------------------------- -- -- Структура таблицы `products` -- CREATE TABLE `products` ( `id` int(8) NOT NULL, `name_product` text NOT NULL COMMENT 'Наименование продукции' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Продукция'; -- -- Дамп данных таблицы `products` -- INSERT INTO `products` (`id`, `name_product`) VALUES (1, 'Бабушкины носки'), (2, 'Свитера с катушками'), (3, 'Не должно выводиться'); -- -------------------------------------------------------- -- -- Структура таблицы `relations` -- CREATE TABLE `relations` ( `id` int(11) NOT NULL, `pokazatel_id` int(8) NOT NULL, `product_id` int(8) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Дамп данных таблицы `relations` -- INSERT INTO `relations` (`id`, `pokazatel_id`, `product_id`) VALUES (1, 1, 1), (2, 1, 2); -- -- Индексы сохранённых таблиц -- -- -- Индексы таблицы `pokazateli` -- ALTER TABLE `pokazateli` ADD PRIMARY KEY (`id`); -- -- Индексы таблицы `products` -- ALTER TABLE `products` ADD PRIMARY KEY (`id`); -- -- Индексы таблицы `relations` -- ALTER TABLE `relations` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT для сохранённых таблиц -- -- -- AUTO_INCREMENT для таблицы `pokazateli` -- ALTER TABLE `pokazateli` MODIFY `id` int(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; -- -- AUTO_INCREMENT для таблицы `products` -- ALTER TABLE `products` MODIFY `id` int(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- AUTO_INCREMENT для таблицы `relations` -- ALTER TABLE `relations` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; COMMIT;
не очень понял чего хотите в результате но Код (Text): SELECT p.name_pokazatel, GROUP_CONCAT(pr.name_product) FROM pokazateli AS p LEFT JOIN relations AS r ON p.id = r.pokazatel_id LEFT JOIN products AS pr ON pr.id = r.product_id GROUP BY p.id дает что-то https://yadi.sk/d/n7ghr9AMCUvUGg
а что надо то? результат то какой должен быть? --- Добавлено --- если пустой не должен выводиться - то Код (Text): SELECT p.name_pokazatel, GROUP_CONCAT(pr.name_product) FROM pokazateli AS p JOIN relations AS r ON p.id = r.pokazatel_id LEFT JOIN products AS pr ON pr.id = r.product_id GROUP BY p.id
@ADSoft Мне нужно вытащить в одном запросе название показателя - одна строка, продукцию к этому показателю - вторая строка. Чтобы я мог в этом же запросе добавить ORDER BY <первая строка> или ORDER BY <вторая строка>. Ну то есть, сортировать запрос либо по названию показателя, либо по списку продукции. --- Добавлено --- Показатель должен выводиться всегда. А продукция - если есть.
@Nerfed, не создавай ни себе ни нам "проблему молотка". Описывай сразу, что ты хочешь сделать на начальном этапе, а не финальную часть своего решения, которое может быть в корне неправильным. Скажу по секрету, что запросов без сортировки не бывает (99%). Ты можешь сортировать по названию показателя, но по группировке не сможешь, можно только отсортировать внутри каждой группы. И эти сортировки грубо говоря никак не влияют одна на другую.
Слушай, я попробовал вот этот твой запрос - у меня выдаёт другой результат. Я немного изменил запрос: Код (Text): SELECT p.name_pokazatel, GROUP_CONCAT(pr.name_product) AS `all_products` FROM pokazateli AS p LEFT JOIN relations AS r ON p.id = r.pokazatel_id LEFT JOIN products AS pr ON pr.id = r.product_id GROUP BY p.id ORDER BY `all_products` Тут вывод просто через var_export: Вроде работает как надо. Только я не понимаю как мне к этому запросу теперь добавить ещё один такой же. Продукция - это только пример. У меня кроме неё ещё 5 таблиц, которые нужно также присобачить в этот запрос. Например, есть регламенты: reglaments - id - name И будет например вторая связывающая таблица: relations_reg - id - pokazatel_id - reglament_id --- Добавлено --- Вроде из первого поста всё понятно. Мне просто нужно вывести список показателей таблицей. Первая колонка - название показателя. Вторая колонка - список продукции, который может быть не всегда. Третья колонка - регламенты, которые тоже могут быть не всегда и т.п. И всё это я хочу вывести одним запросом, склеив дочерние элементы показателя в строки, чтобы по ним можно было делать ORDER BY прямо в этом запросе.
Я немного причесал структуру. Код (Text): -- phpMyAdmin SQL Dump -- version 4.8.5 -- https://www.phpmyadmin.net/ -- -- Хост: localhost -- Время создания: Дек 05 2019 г., 17:19 -- Версия сервера: 5.7.25 -- Версия PHP: 7.0.8 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET AUTOCOMMIT = 0; START TRANSACTION; SET time_zone = "+00:00"; -- -- База данных: `testy-test` -- -- -------------------------------------------------------- -- -- Структура таблицы `pokazateli` -- CREATE TABLE `pokazateli` ( `id` int(8) NOT NULL, `name_pokazatel` text NOT NULL COMMENT 'id названия показателя' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Показатели'; -- -- Дамп данных таблицы `pokazateli` -- INSERT INTO `pokazateli` (`id`, `name_pokazatel`) VALUES (1, 'Ворсистость'), (2, 'Пустой'); -- -------------------------------------------------------- -- -- Структура таблицы `products` -- CREATE TABLE `products` ( `id` int(8) NOT NULL, `name_product` text NOT NULL COMMENT 'Наименование продукции' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Продукция'; -- -- Дамп данных таблицы `products` -- INSERT INTO `products` (`id`, `name_product`) VALUES (1, 'Бабушкины носки'), (2, 'Свитера с катушками'), (3, 'Не должно выводиться'); -- -------------------------------------------------------- -- -- Структура таблицы `reglaments` -- CREATE TABLE `reglaments` ( `id` int(11) NOT NULL, `name_reglament` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Дамп данных таблицы `reglaments` -- INSERT INTO `reglaments` (`id`, `name_reglament`) VALUES (1, 'первый'), (2, 'второй'), (3, 'третий'), (4, 'четвёртый'); -- -------------------------------------------------------- -- -- Структура таблицы `rel_products` -- CREATE TABLE `rel_products` ( `id` int(11) NOT NULL, `pokazatel_id` int(8) NOT NULL, `product_id` int(8) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Дамп данных таблицы `rel_products` -- INSERT INTO `rel_products` (`id`, `pokazatel_id`, `product_id`) VALUES (1, 1, 1), (2, 1, 2), (3, 2, 1); -- -------------------------------------------------------- -- -- Структура таблицы `rel_reglaments` -- CREATE TABLE `rel_reglaments` ( `id` int(11) NOT NULL, `pokazatel_id` int(11) NOT NULL, `reglament_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Дамп данных таблицы `rel_reglaments` -- INSERT INTO `rel_reglaments` (`id`, `pokazatel_id`, `reglament_id`) VALUES (1, 2, 1), (2, 2, 3), (3, 1, 4); -- -- Индексы сохранённых таблиц -- -- -- Индексы таблицы `pokazateli` -- ALTER TABLE `pokazateli` ADD PRIMARY KEY (`id`); -- -- Индексы таблицы `products` -- ALTER TABLE `products` ADD PRIMARY KEY (`id`); -- -- Индексы таблицы `reglaments` -- ALTER TABLE `reglaments` ADD PRIMARY KEY (`id`); -- -- Индексы таблицы `rel_products` -- ALTER TABLE `rel_products` ADD PRIMARY KEY (`id`); -- -- Индексы таблицы `rel_reglaments` -- ALTER TABLE `rel_reglaments` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT для сохранённых таблиц -- -- -- AUTO_INCREMENT для таблицы `pokazateli` -- ALTER TABLE `pokazateli` MODIFY `id` int(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; -- -- AUTO_INCREMENT для таблицы `products` -- ALTER TABLE `products` MODIFY `id` int(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- AUTO_INCREMENT для таблицы `reglaments` -- ALTER TABLE `reglaments` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5; -- -- AUTO_INCREMENT для таблицы `rel_products` -- ALTER TABLE `rel_products` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- AUTO_INCREMENT для таблицы `rel_reglaments` -- ALTER TABLE `rel_reglaments` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; COMMIT; Попробовал сделать ещё с таблицей регламентов так - не работает. PHP: SELECT p.name_pokazatel, GROUP_CONCAT(pr.name_product) AS `all_products`, GROUP_CONCAT(reg.name_reglament) AS `all_reglaments` FROM pokazateli AS p LEFT JOIN rel_products AS r ON p.id = r.pokazatel_id LEFT JOIN products AS pr ON pr.id = r.product_id LEFT JOIN rel_reglaments AS r2 ON p.id = r2.pokazatel_id LEFT JOIN reglaments AS reg ON reg.id = r2.reglament_id GROUP BY p.id ORDER BY `all_products` Выводит лишние результаты. Должно быть: Пустой -- Бабушкины носки -- первый,третий Ворсистость -- Бабушкины носки,Свитера с катушками -- четвёртый Как я понимаю, это из-за GROUP BY.
нет, это из-за того что ты не умеешь SQL)) его, как ни странно это звучит, но тоже надо изучать и желательно как отдельный язык программирования, без этого так и будешь лепить "горбатого к стенке". Нельзя просто скописастить половину запроса и прилепить. Я уже сказал, сначала надо создать архитектуру БД, и не просто "причесать" от балды, а опираясь на правила и законы. Чётко определить отношения. И понимать что и откуда выбирать и как это будет выглядеть в итоге. И как из этого уже получить желаемое.