Selecting All Rows With Multiple Conditions In 2 Other Tables

[phdphd]

Hi All,

I am trying to find a way to select all rows in one table that meet multiple conditions in 2 other tables, using a combination of inner join, group by, having count syntax. I know how to do it with just one other table, but not with 2.

There is a good fiddle example with one table here : http://sqlfiddle.com/#!2/58b4b1/8

I tried to create and fill another table, then edit the existing query accordingly, but I always get a syntax error message.

I do not even know if it is technically feasible.

Thanks for your help !

 

[Barand]

What is the structure of the two tables?

What conditions are you searching for?

[phdphd]

Based on the fiddle provided, let's say the tables have following structure and contents:

-- -----------------------------------------------------
-- 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;

-- -----------------------------------------------------
-- Table `properties2`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `properties2` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `product_id` INT NULL,
  `continent` VARCHAR(45) NULL,
  `country` 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');

-- -----------------------------------------------------
-- Data for table `properties2`
-- -----------------------------------------------------
INSERT INTO `properties2` (`id`, `product_id`, `continent`, `country`) VALUES (1, 1, 'Asia', 'China');
INSERT INTO `properties2` (`id`, `product_id`, `continent`, `country`) VALUES (2, 1, 'Asia', 'India');
INSERT INTO `properties2` (`id`, `product_id`, `continent`, `country`) VALUES (3, 2, 'Asia', 'China');
INSERT INTO `properties2` (`id`, `product_id`, `continent`, `country`) VALUES (4, 1, 'Europe', 'France');
INSERT INTO `properties2` (`id`, `product_id`, `continent`, `country`) VALUES (5, 2, 'Europe', 'UK');

 

Let's say I want to find products in table "products" that have Format/Book and Format/Audio as key/value (table "properties"), and Asia/China and Asia/India as continent/country (table "properties2").

 

The following does not work :

select p.id, p.name
from products p
inner join properties t
  on p.id = t.product_id
inner join properties2 t2
  on p.id = t2.product_id  
where  t.value in ('Book', 'Audio')
group by p.id, p.name
having count(distinct t.value) = 2
and
t2.country in ('China', 'India')
group by p.id, p.name
having count(distinct t2.country) = 2

 

I get the message 'check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by p.id, p.name having count(distinct t2.country) = 2"

 

Thanks.

Edited August 16, 2015 by phdphd

[Barand]

try

select p.id
, p.name
from products p
inner join 
	(
    SELECT product_id as id
    FROM properties
    WHERE `key` = 'Format' AND value = 'Book'
    ) as book USING (id)
inner join 
	(
    SELECT product_id as id
    FROM properties
    WHERE `key` = 'Format' AND value = 'Audio'
    ) as audio USING (id)
inner join 
	(
    SELECT product_id as id
    FROM properties2
    WHERE continent = 'Asia' AND country = 'India'
    ) as india USING (id)
inner join 
	(
    SELECT product_id as id
    FROM properties2
    WHERE continent = 'Asia' AND country = 'China'
    ) as china USING (id);

[phdphd]

Thank you!