Mysql – Select all rows with multiple conditions in another table

join;MySQL

Here is my simplified database scheme

-- -----------------------------------------------------
-- Table `products`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `products` (
  `id` INT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `properties`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `properties` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `product_id` INT NULL,
  `key` VARCHAR(45) NULL,
  `value` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Data for table `products`
-- -----------------------------------------------------
INSERT INTO `products` (`id`, `name`) VALUES (1, 'English Book with AudioCD');
INSERT INTO `products` (`id`, `name`) VALUES (2, 'Polish Book');


-- -----------------------------------------------------
-- Data for table `properties`
-- -----------------------------------------------------
INSERT INTO `properties` (`id`, `product_id`, `key`, `value`) VALUES (1, 1, 'Format', 'Book');
INSERT INTO `properties` (`id`, `product_id`, `key`, `value`) VALUES (2, 1, 'Format', 'Audio');
INSERT INTO `properties` (`id`, `product_id`, `key`, `value`) VALUES (3, 2, 'Format', 'Book');
INSERT INTO `properties` (`id`, `product_id`, `key`, `value`) VALUES (4, 1, 'Language', 'English');
INSERT INTO `properties` (`id`, `product_id`, `key`, `value`) VALUES (5, 2, 'Language', 'Polish');

And here are tables representations

+----+--------------------------+
| id |           name           |
+----+--------------------------+
|  1 | English Book wit AudioCD |
|  2 | Polish Book              |
+----+--------------------------+

+----+------------+----------+---------+
| id | product_id |   key    |  value  |
+----+------------+----------+---------+
|  1 |          1 | Format   | Book    |
|  2 |          1 | Format   | AudioCD |
|  3 |          2 | Format   | Book    |
|  4 |          1 | Language | English |
|  5 |          2 | Language | Polish  |
+----+------------+----------+---------+

I need to select all products that have key 'Format' and value 'Book' and 'AudioCD'. But I need only products that met all of these conditions. So I want only product_id 1, not 2 (beacuse it doesn't have AudioCD). Additionaly I want might to add also Language key to that.

Is there way to do it without multiple JOINS?
The problem with multiple JOINS starts where I would have let's say 20-30 combinations of key-value attributes, because they will be added freely.

Appreciate any hints!

Best Answer

You can get the result by using a combination of a WHERE, GROUP BY, and HAVING to return the products that have both the Book and Audio values.

select p.id, p.name
from products p
inner join properties t
  on p.id = t.product_id
where t.key = 'Format'
  and t.value in ('Book', 'Audio')
group by p.id, p.name
having count(distinct t.value) = 2;

See SQL Fiddle with Demo. Basically, you'll place the values that you want in the WHERE clause - this would be the Book and Audio. Then you'll GROUP BY the product.id and product.name these are unique to each product. Finally you will use the HAVING clause to count the distinct properties.value that are returned. This should equal the number of items that you have in your WHERE clause.

This could also be written using conditional aggregation inside of the HAVING:

select p.id, p.name
from products p
inner join properties t
  on p.id = t.product_id
group by p.id, p.name
having sum(case when t.key = 'Format' and t.value = 'Book' then 1 else 0 end) > 0
  and sum(case when t.key = 'Format' and t.value = 'Audio' then 1 else 0 end) > 0

See SQL Fiddle with Demo

If you want to include the Language key, then you can use conditional aggregation in the HAVING clause:

select p.id, p.name
from products p
inner join properties t
  on p.id = t.product_id
group by p.id, p.name
having 
  sum(case when t.key = 'Format' and t.value = 'Book' then 1 else 0 end) > 0
  and sum(case when t.key = 'Format' and t.value = 'Audio' then 1 else 0 end) > 0
  and sum(case when t.key = 'Language' and t.value = 'English' then 1 else 0 end) > 0

See Demo

Related Question