Jump to content

Selecting All Rows With Multiple Conditions In 2 Other Tables


phdphd
Go to solution Solved by Barand,

Recommended Posts

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 !

 

Link to comment
Share on other sites

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 by phdphd
Link to comment
Share on other sites

  • Solution

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);
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.