powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Фильтрация данных при связи много к многим
7 сообщений из 7, страница 1 из 1
Фильтрация данных при связи много к многим
    #40068143
TomYoll
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
У меня в базе данных аптеки есть 4 таблицы:

продукт (id продукта, имя и т.д)

продукт_заболевание - таблица для связи много ко многим, в ней id продукта, id заболевания

заболевания - (id заболевания, имя, его категория)

категории заболеваний - (id категории, имя)

Таблицы связаны между собой (во вложении показано как). Я пытаюсь реализовать фильтрацию и выбрать из таблицы имя того продукта, категория заболевания которого равна какому-либо значению, например "Тест". Собственно, вопрос: как построить такой запрос? Прбовал так:
Код: sql
1.
SELECT products.name FROM `products`, `diseases_category` WHERE `diseases_category`.`name` = "Тест"


Но получаю просто названия всех продуктов в таблице.
Также прикладываю дамп бд с этими таблицами:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
 
CREATE DATABASE IF NOT EXISTS `Example` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `Example`;
 
CREATE TABLE `diseases` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `diseases` (`id`, `name`) VALUES
(1, 'Заболевание1'),
(2, 'Заболевание2'),
(3, 'Заболевание3'),
(4, 'Заболевание4'),
(5, 'Заболевание5\r\n'),
(6, 'Заболевание6'),
(7, 'Тест');
 
CREATE TABLE `diseases_category` (
  `id_category` int(11) NOT NULL,
  `name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `diseases_category` (`id_category`, `name`) VALUES
(1, 'Категория1'),
(2, 'Категория2'),
(3, 'Категория3'),
(4, 'Категория4'),
(5, 'Категория5'),
(6, 'Категрия6'),
(7, 'Категрия7'),
(8, 'Категрия8'),
(9, 'Категрия9'),
(10, 'Тест');
 
CREATE TABLE `diseases_product` (
  `id_disease` int(11) NOT NULL,
  `id_product` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `diseases_product` (`id_disease`, `id_product`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 1),
(6, 2),
(7, 2);
 
CREATE TABLE `products` (
  `product_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL DEFAULT 'Без назви',
  `manufacturer` varchar(255) DEFAULT NULL,
  `release_form` varchar(255) DEFAULT NULL,
  `image_path` varchar(100) DEFAULT NULL,
  `Instruction` text,
  `cooking_method` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `products` (`product_id`, `name`, `manufacturer`, `release_form`, `image_path`, `Instruction`, `cooking_method`) VALUES
(1, 'Продукт1', NULL, NULL, NULL, NULL, NULL),
(2, 'Тест', NULL, NULL, NULL, NULL, NULL);
 
 
ALTER TABLE `diseases`
  ADD PRIMARY KEY (`id`);
 
ALTER TABLE `diseases_category`
  ADD PRIMARY KEY (`id_category`);
 
ALTER TABLE `diseases_product`
  ADD KEY `id_product` (`id_product`),
  ADD KEY `id_disease` (`id_disease`);
 
ALTER TABLE `products`
  ADD PRIMARY KEY (`product_id`);
 
 
ALTER TABLE `diseases`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
ALTER TABLE `diseases_category`
  MODIFY `id_category` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
ALTER TABLE `products`
  MODIFY `product_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
 
ALTER TABLE `diseases`
  ADD CONSTRAINT `diseases_ibfk_1` FOREIGN KEY (`id`) REFERENCES `diseases_category` (`id_category`) ON DELETE CASCADE ON UPDATE CASCADE;
 
ALTER TABLE `diseases_product`
  ADD CONSTRAINT `diseases_product_ibfk_1` FOREIGN KEY (`id_disease`) REFERENCES `diseases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `diseases_product_ibfk_2` FOREIGN KEY (`id_product`) REFERENCES `products` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;
 
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
...
Рейтинг: 0 / 0
Фильтрация данных при связи много к многим
    #40068145
Gluck99
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
TomYoll
Прбовал так:
Код: sql
1.
SELECT products.name FROM `products`, `diseases_category` WHERE `diseases_category`.`name` = "Тест"


Но получаю просто названия всех продуктов в таблице.

Во-первых, какой результат вы надеялись получить, если у вас отношения между продуктами и заболеваниями хранятся в таблице diseases_product, а выборку вы делаете из products и diseases_category.
Во-вторых, у вас отсутствует таблица связей между категориями и болезнями или категориями и продуктами. Т.е. непонятно, какое заболевание в какой категории находится или какая категория относится к какому продукту.

По той структуре, которая у вас сейчас в наличии, можно выбрать продукты, которые относятся к конкретному заболеванию.

А вообще имеет смысл отталкиваться от задачи и переделать БД, пока ещё идёт стадия прототипа. А то не дай бог это не учебный проект. Тем более на такую тематику. Я бы в такой аптеке не рискнул закупаться.
...
Рейтинг: 0 / 0
Фильтрация данных при связи много к многим
    #40068151
TomYoll
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Gluck99

Во-вторых, у вас отсутствует таблица связей между категориями и болезнями или категориями и продуктами. Т.е. непонятно, какое заболевание в какой категории находится или какая категория относится к какому продукту.

добавил таблицу связей между заболеванием и категорией, таблица категорий теперь просто category, а свзязанная таблица теперь category_product.
Gluck99
А вообще имеет смысл отталкиваться от задачи и переделать БД, пока ещё идёт стадия прототипа. А то не дай бог это не учебный проект. Тем более на такую тематику. Я бы в такой аптеке не рискнул закупаться.

Это всё же учёбная база данных, так-что волноваться не о чём (наверное). А БД эту я переделывал уже не раз, это пока лучший вариант т.к в sql я новичок (думаю, это заметно).
Дамп
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

CREATE DATABASE IF NOT EXISTS `Example` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `Example`;

CREATE TABLE `category` (
  `id_category` int(11) NOT NULL,
  `name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `category` (`id_category`, `name`) VALUES
(1, 'Категория1'),
(2, 'Категория2'),
(3, 'Категория3'),
(4, 'Категория4'),
(5, 'Категория5'),
(6, 'Категрия6'),
(7, 'Категрия7'),
(8, 'Категрия8'),
(9, 'Категрия9'),
(10, 'Тест');

CREATE TABLE `diseases` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `diseases` (`id`, `name`) VALUES
(1, 'Заболевание1'),
(2, 'Заболевание2'),
(3, 'Заболевание3'),
(4, 'Заболевание4'),
(5, 'Заболевание5\r\n'),
(6, 'Заболевание6'),
(7, 'Тест');

CREATE TABLE `diseases_category` (
  `id_category` int(11) NOT NULL,
  `id_disease` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `diseases_category` (`id_category`, `id_disease`) VALUES
(1, 1);

CREATE TABLE `diseases_product` (
  `id_disease` int(11) NOT NULL,
  `id_product` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `diseases_product` (`id_disease`, `id_product`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 1),
(6, 2),
(7, 2);

CREATE TABLE `products` (
  `product_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL DEFAULT 'Без назви',
  `manufacturer` varchar(255) DEFAULT NULL,
  `release_form` varchar(255) DEFAULT NULL,
  `image_path` varchar(100) DEFAULT NULL,
  `Instruction` text,
  `cooking_method` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `products` (`product_id`, `name`, `manufacturer`, `release_form`, `image_path`, `Instruction`, `cooking_method`) VALUES
(1, 'Продукт1', NULL, NULL, NULL, NULL, NULL),
(2, 'Тест', NULL, NULL, NULL, NULL, NULL);


ALTER TABLE `category`
  ADD PRIMARY KEY (`id_category`);

ALTER TABLE `diseases`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `diseases_category`
  ADD KEY `id_disease` (`id_disease`),
  ADD KEY `id_category` (`id_category`);

ALTER TABLE `diseases_product`
  ADD KEY `id_product` (`id_product`),
  ADD KEY `id_disease` (`id_disease`);

ALTER TABLE `products`
  ADD PRIMARY KEY (`product_id`);


ALTER TABLE `category`
  MODIFY `id_category` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
ALTER TABLE `diseases`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
ALTER TABLE `products`
  MODIFY `product_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

ALTER TABLE `diseases_category`
  ADD CONSTRAINT `diseases_category_ibfk_1` FOREIGN KEY (`id_disease`) REFERENCES `diseases` (`id`),
  ADD CONSTRAINT `diseases_category_ibfk_2` FOREIGN KEY (`id_category`) REFERENCES `category` (`id_category`);

ALTER TABLE `diseases_product`
  ADD CONSTRAINT `diseases_product_ibfk_1` FOREIGN KEY (`id_disease`) REFERENCES `diseases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `diseases_product_ibfk_2` FOREIGN KEY (`id_product`) REFERENCES `products` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

...
Рейтинг: 0 / 0
Фильтрация данных при связи много к многим
    #40068174
Gluck99
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
TomYoll
добавил таблицу связей между заболеванием и категорией, таблица категорий теперь просто category, а свзязанная таблица теперь category_product.
Т.е. какое-то конкретное заболевание может входить одновременно в несколько категорий? Если всё-таки нет, и каждое заболевание может быть отнесено только к какой-то одной категории, то у вас ошибка.
TomYoll
в sql я новичок (думаю, это заметно).
Так вопрос к SQL имеет косвенное отношение. Задача - правильно определить сущности и связи между ними.
...
Рейтинг: 0 / 0
Фильтрация данных при связи много к многим
    #40068179
TomYoll
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Gluck99
Т.е. какое-то конкретное заболевание может входить одновременно в несколько категорий? Если всё-таки нет, и каждое заболевание может быть отнесено только к какой-то одной категории, то у вас ошибка.

Только к одной категории, в чём тут ошибка?
...
Рейтинг: 0 / 0
Фильтрация данных при связи много к многим
    #40068226
Gluck99
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
TomYoll
Только к одной категории, в чём тут ошибка?
Ошибка или нет зависит от конкретной реализации, но в данном частном случае, думаю, да.
Если заболевание может находиться только в одной категории, в чём смысл связующей таблицы diseases_category?

У нас есть таблица категорий заболеваний с ID категории. Есть таблица заболеваний, у каждого заболевания свой ID, а также CategoryID. Далее таблица с продуктами и таблица связи заболеваний и продуктов. Итого 4 таблицы. Нужно еще добавить таблицу со справочником производителей. Хранить наименование производителя в текстовом поле - это жуткий моветон.

Прошу обратить внимание на нейминг (наименование полей), нейминг должен быть однозначным и производиться по единым правилам для всей базы.

Структура БД
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
CREATE TABLE `disease_categories` (
  `ID` int(11) NOT NULL,
  `CategoryName` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `diseases` (
  `ID` int(11) NOT NULL,
  `CategoryID` int(11) NOT NULL,
  `DiseaseName` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `products` (
  `ID` int(11) NOT NULL,
  `ManufacturerID` int(11) DEFAULT NULL, -- Производителей необходимо вынести в отдельную таблицу и хранить в таблице продуктов ID производителя
  `ProductName` varchar(100) NOT NULL DEFAULT 'no name',
  `ReleaseForm` varchar(255) DEFAULT NULL,
  `ImagePath` varchar(100) DEFAULT NULL,
  `Instruction` text,
  `CookingMethod` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `diseases_product` (
  `DiseaseID` int(11) NOT NULL,
  `ProductID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Теперь можно приступать к решению основной задачи с выборкой данных.
...
Рейтинг: 0 / 0
Фильтрация данных при связи много к многим
    #40068564
TomYoll
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Gluck99, спасибо большое за помощь, взял предоставленные вами таблицы, связал их и использовал такой запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT
  products.ProductName
FROM diseases_product
  INNER JOIN diseases
    ON diseases_product.DiseaseID = diseases.ID
  INNER JOIN disease_categories
    ON diseases.CategoryID = disease_categories.ID
  INNER JOIN products
    ON diseases_product.ProductID = products.ID
    WHERE disease_categories.CategoryName = "Категория1"


Всё работает, ещё раз спасибо.
...
Рейтинг: 0 / 0
7 сообщений из 7, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Фильтрация данных при связи много к многим
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]