matching a one to many relationship exactly.

[Jessica]

I have two tables which contains one-to-many relationship data. I've simplified this to explain it.

 

Parent Table

parent_id

parent_name

 

Children Table

child_id

parent_id

child_name

 

 

I have a php form which submits a list of children ids. I want to write a query which can tell me which parents have exactly these children. So for example I have parent_id:1 and there are two children child_id 1 and 2. 

 

If I submit child_id:2, I want no results. If I submit 1 and 2 I want parent:1. If I submit 1, 2,3 I need no results.

 

So far I've got my PHP to generate a query to perform multiple inner joins on parent-child, but that doesn't tell me if I've submitted 1 but I need 1 and 2. It would work for submitting 1,2,3 and only needing 1,2, but it doesn't work for too few children submitted. 

 

Example:

SELECT parent_id FROM parent

INNER JOIN child

ON child.parent_id = parent.parent_id

AND child.child_id = 1

 

This will return 1, but I need no rows.

 

SELECT parent_id FROM parent

INNER JOIN child

ON child.parent_id = parent.parent_id

AND child.child_id = 1

INNER JOIN child

ON child.parent_id = parent.parent_id

AND child.child_id = 2

 

Will return 1, which is right.

 

 

 

 

Any ideas? 

 

[Jessica]

So, using GROUP_CONCAT I can get close with this SQL:

SELECT parent.parent_id, GROUP_CONCAT(child.child_id ORDER BY child.child_id ASC) AS child_ids
FROM parent
INNER JOIN child
ON parent.parent_id = child.parent_id
GROUP BY parent.parent_id

 

But it won't let me use the GROUP_CONCAT in a where clause so I can compare. 

[Christian F.]

Here you go:

SELECT parent.id, GROUP_CONCAT(child.id ORDER BY child.id ASC) AS child_ids
FROM parent
INNER JOIN child on parent.id=child.parent_id
GROUP BY parent.id
HAVING child_ids = "1,2";

The trick is using "HAVING" instead of "WHERE", so you can reference the aliases.

[Jessica]

I had to do a double take when it wasn't Barand solving this for me. Thank you so much Christian!

[Christian F.]

Hehe, yeah, I know what you mean. I'm surprised I managed to get in before him as well.

 

You're most welcome, glad I could help (as always).

[Barand]

I've just this minute seen it. Nice one CF

[Jessica]

OK so it worked with the first child table, but now that I have added a second type of child table, it's not working as I expected. This is really complicated.  

 

I am trying to find recipe id #3 based on matching it's one recipe_ingredient and two recipe_settings. 

 

Table Structures:

CREATE TABLE IF NOT EXISTS `recipes` (
  `recipe_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ingredient_id` varchar(100) NOT NULL,
  `appliance_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`recipe_id`),
  KEY `appliance_id` (`appliance_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `recipes` (`recipe_id`, `ingredient_id`, `appliance_id`) VALUES
(1, '5', 1),
(2, '6', 1),
(3, '7', 2); -- this one

CREATE TABLE IF NOT EXISTS `recipe_ingredients` (
  `recipe_id` int(10) unsigned NOT NULL,
  `ingredient_id` int(10) unsigned NOT NULL,
  `quantity` int(10) unsigned NOT NULL,
  PRIMARY KEY (`recipe_id`,`ingredient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `recipe_ingredients` (`recipe_id`, `ingredient_id`, `quantity`) VALUES
(1, 1, 2),
(1, 3, 2),
(2, 2, 2),
(2, 3, 1),
(3, 5, 1); -- this one

CREATE TABLE IF NOT EXISTS `recipe_settings` (
  `recipe_id` int(10) unsigned NOT NULL,
  `appliance_setting_id` int(10) unsigned NOT NULL,
  `appliance_setting_option_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `recipe_settings` (`recipe_id`, `appliance_setting_id`, `appliance_setting_option_id`) VALUES
(3, 2, 3), -- this one
(3, 3, 5); -- this one

 

The query I generate in PHP, which returns no results

SELECT recipes.recipe_id,  
GROUP_CONCAT(recipe_ingredients.ingredient_id ORDER BY recipe_ingredients.ingredient_id ASC) AS ingredient_ids,
GROUP_CONCAT(recipe_ingredients.quantity ORDER BY recipe_ingredients.ingredient_id ASC) AS quantities,
GROUP_CONCAT(recipe_settings.appliance_setting_id ORDER BY recipe_settings.appliance_setting_id ASC) AS appliance_settings,
GROUP_CONCAT(recipe_settings.appliance_setting_option_id ORDER BY recipe_settings.appliance_setting_option_id ASC) AS appliance_options,
recipes.ingredient_id 

FROM recipes

INNER JOIN recipe_ingredients
ON recipe_ingredients.recipe_id = recipes.recipe_id

INNER JOIN recipe_settings
ON recipe_settings.recipe_id = recipes.recipe_id

INNER JOIN ingredients
ON ingredients.ingredient_id = recipes.ingredient_id

WHERE appliance_id = 2
GROUP BY recipes.recipe_id

HAVING ingredient_ids = '5'
AND quantities = '1'
AND appliance_settings = '2,3'
AND appliance_options = '3,5'

 

If I run the query without the HAVING clauses I get a row, but it has '5,5' and '1,1' where I expected just 5 and 1. The 2,3 and 3,5 parts are correct.

recipe_id    3
ingredient_ids    5,5
quantities    1,1
appliance_settings    2,3
appliance_options    3,5
ingredient_id    7

 

I suspect this is because there are two children in recipe_settings and only one in recipe_ingredients for recipe #3. So how do I get it to only count/group_concat the 5 and 1 one time each?

 

SELECT * 
FROM recipes
INNER JOIN recipe_ingredients ON recipe_ingredients.recipe_id = recipes.recipe_id
INNER JOIN recipe_settings ON recipe_settings.recipe_id = recipes.recipe_id
INNER JOIN ingredients ON ingredients.ingredient_id = recipes.ingredient_id
WHERE appliance_id =2

recipe_id    3    3
ingredient_id    7    7
appliance_id    2    2
ingredient_id    5    5
quantity    1    1
appliance_setting_id    2    3
appliance_setting_option_id    3    5
ingredient_id    7    7

[Jessica]

Oh fuck me. 

 

I thought I tried this, but after double checking the comments in the manual say you can use DISTINCT inside the GROUP_CONCAT. 

 

SELECT recipes.recipe_id,  
GROUP_CONCAT(DISTINCT recipe_ingredients.ingredient_id ORDER BY recipe_ingredients.ingredient_id ASC) AS ingredient_ids,
GROUP_CONCAT(DISTINCT recipe_ingredients.quantity ORDER BY recipe_ingredients.ingredient_id ASC) AS quantities,
GROUP_CONCAT(DISTINCT recipe_settings.appliance_setting_id ORDER BY recipe_settings.appliance_setting_id ASC) AS appliance_settings,
GROUP_CONCAT(DISTINCT recipe_settings.appliance_setting_option_id ORDER BY recipe_settings.appliance_setting_option_id ASC) AS appliance_options,
recipes.ingredient_id 

FROM recipes

etc....

 

Works. *headdesk*

[Christian F.]

*Christian puts a pillow on the desk just before Jessica's head hits..

[Barand]

Jessica, can I ask why

• table recipes contains column ingredient_id
  
• ingredient_id in recipes is varchar(100) and int in recipe_ingredients table
  

Are you sure about your normalization?

[Jessica]

Jessica, can I ask why

• table recipes contains column ingredient_id
  
• ingredient_id in recipes is varchar(100) and int in recipe_ingredients table
  

Are you sure about your normalization?

 

1. A recipe produces a new ingredient. Combine a and b to make c.

2. That was a mistake, thank you! Fixed.

[Barand]

1. A recipe produces a new ingredient. Combine a and b to make c.

 

I see - like a sub-assembly in a stock file

 

eg An ingredient of lasagne is bechemel sauce which is another recipe

Edited February 8, 2013 by Barand

[Jessica]

I have no idea what you mean but if you understood me then we're good

[Barand]

I added an edit to my previous post by way of explanation

[fenway]

Just a note of caution -- GROUP_CONCAT has a maxlength of 1000 characters by default, so if you have a great many records, it will be silently truncated.

[Jessica]

Thanks Fenway! Should be no more than 5 children for each parent so I think it will work then!

[DSTR3]

Looks like a "Silver Platter" to me!

[Jessica]

Then you clearly don't know what that phrase means.

[Christian F.]

To be honest, you did serve all of the required information to us on a silver platter, Jessica.

However, I don't think that was what he was trying to say.