How to select "products" containing several "parameters" at the same time?

Question

MySQL

Hey!
There are goods ("article" table) and their parameters ("article_param" table).
Each product can contain from 1 to X parameters.
I store the binding of parameters to products in the third table "article_param_item" (id, id_article, id_param).
Selecting all products containing one parameter is easy:

SELECT * FROM article AS a, article_param_item AS api 
WHERE a.id = api.id_article AND api.id=15 
GROUP BY a.id

But how to select products that satisfy the characteristics id_param=15 and id_param=16 at the same time?

Answer 1

The simplest is to replace AND api.id=15 with AND api.id IN (list of required id)

Answer 2

Something like this (from the point of view of optimization, it is still worth thinking about, but the principle is working)

-- Создаем таблицы
-- Таблица товаров
Create table article (
id_article int identity(1,1),
Name_article NVarchar(20))

-- Таблица парметров 
Create table article_param (
id_param int identity(1,1),
Name_param Nvarchar (100))

-- Таблица соотношения
CREATE TABLE dbo.article_param_item
(
  id int identity(1,1),
   id_article  int not NULL,
   id_param  int not NULL
)


 truncate table article
 truncate table article_param
 truncate table article_param_item


-- Заполняем таблицы данным для примера
INSERT INTO [dbo].[article] (Name_article) VALUES (N'Утюг')
INSERT INTO [dbo].[article] (Name_article) VALUES (N'Стол')
INSERT INTO [dbo].[article] (Name_article) VALUES (N'Телевизор')

INSERT INTO [dbo].[article_param](Name_param)VALUES (N'Пластиковый')
INSERT INTO [dbo].[article_param](Name_param)VALUES (N'Плоский')
INSERT INTO [dbo].[article_param](Name_param)VALUES (N'Красный')
INSERT INTO [dbo].[article_param](Name_param)VALUES (N'Бракованный')
INSERT INTO [dbo].[article_param](Name_param)VALUES (N'Новый')

-- Заполняем таблицу соотношений
INSERT INTO [dbo].[article_param_item]([id_article],[id_param])VALUES(1,1) -- Утюг Пластиковый
INSERT INTO [dbo].[article_param_item]([id_article],[id_param])VALUES(1,5) -- Утюг Новый

INSERT INTO [dbo].[article_param_item]([id_article],[id_param])VALUES(2,1) -- Стол Пластиковый
INSERT INTO [dbo].[article_param_item]([id_article],[id_param])VALUES(2,2) -- Стол Плоский
INSERT INTO [dbo].[article_param_item]([id_article],[id_param])VALUES(2,3) -- Стол Бракованный
INSERT INTO [dbo].[article_param_item]([id_article],[id_param])VALUES(2,4) -- Стол Красный

INSERT INTO [dbo].[article_param_item]([id_article],[id_param])VALUES(3,2) -- Телевизор Плоский
INSERT INTO [dbo].[article_param_item]([id_article],[id_param])VALUES(3,5) -- Телевизор Новый


-- Отображаем все товары с их параметрами
SELECT *
  FROM [dbo].[article] art 
  join [article_param_item] rel on art.id_article=rel.id_article
  join [article_param] parametr on parametr.id_param =rel.id_param

-- Для поиска по нескольким параметрам поиска необходимо занести их во временную таблицу
Create table ##Param_table (id_parm int)

-- Допустим ищем товары удовлетворяющие двум параметрам
-- Плоский и Новый
insert into  ##Param_table (id_parm) values (2)
insert into  ##Param_table (id_parm) values (5)

-- Находим все товары удовлетворяющие нашему списку параметров

;with CTE_ArticleInParam as
(
SELECT art.[Name_article],parametr.Name_param
,count (parametr.Name_param) over (partition by art.[Name_article]) as 'Count_Param'
FROM [dbo].[article] art 
join [article_param_item] rel on art.id_article=rel.id_article 
join [article_param] parametr on parametr.id_param =rel.id_param 
join ##Param_table ParTabl on ParTabl.id_parm = rel.id_param 
)

select [Name_article],Name_param,Count_Param
from CTE_ArticleInParam
where Count_Param = (select count (*) from ##Param_table)