How to select multiple values in one column many to many relationship-CodePudding

Table description

CREATE TABLE `attribute` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255)    DEFAULT NULL,
  `value` varchar(255)    DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `product` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255)    DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `product_attribute` (
  `product_id` bigint(20) NOT NULL,
  `attribute_id` bigint(20) NOT NULL,
  KEY `FKefc9famxhv98xs6686269a79` (`attribute_id`),
  KEY `FKlefs59y5kmsbu017n1wp10gf2` (`product_id`),
  CONSTRAINT `FKefc9famxhv98xs6686269a79` FOREIGN KEY (`attribute_id`) REFERENCES `attribute` (`id`),
  CONSTRAINT `FKlefs59y5kmsbu017n1wp10gf2` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `product`VALUES (1,'LAPTOP A'),(2,'LAPTOP B')
INSERT INTO `attribute` VALUES  (1,'branch','Acer'),(2,'branch','Dell'),
                (3,'screen','13 Inch'),(4,'screen','15.6 Inch')     
INSERT INTO `product_attribute` VALUES (1,1),(1,3),(2,2),(2,4)

Example1: I want to select products with attribute name=branch and attribute value=Acer and attribute name=screen and attribute value = 13 Inch

Example2: I want to select products with attribute name=branch and (attribute value=Acer or attribute value=Dell) and attribute name=screen and (attribute value = 13 Inch or attribute value = 15.6 Inch)

possible 3 or more attributes may be required... I tried everything but all failed.

CodePudding user response：

Example - 1 (query) -

select p.* from product p
where p.id in (
select distinct pa.product_id 
from attribute a join product_attribute pa
on 
pa.attribute_id = a.id
and (a.name,a.value) in (('branch','Acer'),('screen','13 Inch'))
)

Example - 1 (Query) - similar to above, slightly different approach

select distinct p.*
from product p join product_attribute pa
on p.id = pa.product_id 
join attribute a
on a.id = pa.attribute_id
and (a.name,a.value) in (('branch','Acer'),('screen','13 Inch'))

Example -2 (query) -

select distinct p.*
from product p join product_attribute pa
on p.id = pa.product_id 
join attribute a
on a.id = pa.attribute_id
and (a.name,a.value) in (('branch','Acer'),('branch','Dell'), ('screen','13 Inch'),('screen','15.6 Inch'))

Modified query to select products with both attributes -

select distinct p.*
from product p join product_attribute pa1
on p.id = pa1.product_id 
join product_attribute pa2
on pa1.product_id = pa2.product_id
and 
pa1.attribute_id in 
(select a.id from attribute a where (a.name,a.value) in (('branch','acer'),('branch','Dell')))
and pa2.attribute_id in 
(select a.id from attribute a where (a.name,a.value) in (('screen','13 Inch'),('screen','15.6 Inch')))

You can refer to the db fiddle here

Required: Matching attrbutes should be atleast given in query or more.

select distinct p.*
from product p 
where p.id in 
(
select pa1.product_id from 
product_attribute pa1,
product_attribute pa2,
product_attribute pa3
where 
pa1.attribute_id in 
(select a.id from attribute a where (a.name,a.value) in (('branch','acer'),('branch','Dell')))
and pa2.attribute_id in
(select a.id from attribute a where (a.name,a.value) in (('screen','13 Inch'),('screen','15.6 Inch')))
and pa3.attribute_id in
(select a.id from attribute a where (a.name,a.value) in (('need','gaming')))
and pa1.product_id = pa2.product_id
and pa2.product_id = pa3.product_id
and pa1.product_id = pa3.product_id
)

Fiddle for above query

Two - Matching attributes:

Four - Matching attributes: