Too many results on a double join

Question

I got 3 tables that i need to get data from:

RECIPE - id, name, worktime, date, describtion

TAGS - id, name

INGREDIENT - id, name, created

Now, both tags and ingredient has a many-to-many relation with recipe.

When i try to query my DB to generate a result, i get way too many results.

SELECT * 
FROM (`recipe` r, `tag` t, `ingredient` i) 
JOIN `recipe_has_tag` rt ON `rt`.`tag_t_id` = `t`.`t_id` 
JOIN `ingredient_has_recipe` ir ON `ir`.`ingredient_in_id` = `i`.`in_id` 
WHERE `r`.`r_id` = ir.recipe_r_id 
AND r.r_id = rt.recipe_r_id 
AND r.r_id = ir.recipe_r_id 
AND r.r_id = 1

I this particular example, i am looking to get 1 recipe, 4 tags and 4 ingredients.

The problem is, instead of returning 4 rows, it mixes the ingredients with the tags, returning 16 results instead of the expected 4.

Right now i end up with 16 results where the each tag has a row with an ingredient, making it 16 results.

Getting something like this (very simplified)

R.name = good dish, i.name = potato, t.name = "potato dish" 
R.name = good dish, i.name = carrot, t.name = "potato dish" 
R.name = good dish, i.name = potato, t.name = "carrot dish" 
R.name = good dish, i.name = carrot, t.name = "carrot dish"

What i want is rows where each row contains 1 tag and 1 ingredient, like this

R.name = good dish, i.name = potato, t.name = "potato dish" 
R.name = good dish, i.name = carrot, t.name = "carrot dish"

And a fiddle for good measure, showing a one line but with too many results in the tags and ingredient column

http://sqlfiddle.com/#!2/626760/2

16 is expected since 1*4*4=16 combinations exists. Could you perhaps add another join between recipie and ingredient? — Fredrik Johansson, Mar 22 '14 at 08:36
Adding to what Fredrik sais: If it is just four records you want, what shall they be: four ingredients or four tags? — Thorsten Kettner, Mar 22 '14 at 08:46
Is there no way to just get the highest amount of rows from a given joined table to be the maximum returned rows and have all the columns from all the involved tables in those rows? If that makes sense — Rasmus, Mar 22 '14 at 08:50
No, sorry, that doesn't make sense to me. Think about what you want to select. Something like "a record for each ingredient of the recipe" or "one line for the recipe name and the tags, plus one line per ingredient" or whatever. So far it is not clear what you want result records you actually want to see. — Thorsten Kettner, Mar 22 '14 at 08:58
Do not mix implicit (comma-) and explicit JOIN syntax. In fact, do not use implicit join syntax at all. — Strawberry, Mar 22 '14 at 09:01
What i want is to join the tables together and get all the related information the a recipe - in this case 4 tags and 4 ingredients - thereby producing 4 rows. From what i can read, a straight join tries to join everything together, so can anyone come with an example on how to produce a result containing everything within 4 rows? If not, what changes should i make to make it possible? — Rasmus, Mar 22 '14 at 09:13
You still don't explain what you want. There are four tags and four ingredients. What makes you say you want four rows? One row per ingredient? One row per tag? If it is one row per ingredient, where to put the tags? Tags and ingredients are not related, so if you just put it all together, you get one record per ingredient-tag combination, i.e. 16 rows. — Thorsten Kettner, Mar 22 '14 at 09:27
if i understand correctly you wants to display the there table of all columns — jmail, Mar 22 '14 at 09:27
@ThorstenKettner Right, i want a n rows, where n is the highest amount of hits returned from any given table that is part of the query. In this case, i want 4 rows which contains all the information from all the tables. To link it to reality - i want to display a recipe with its associated tags and ingredients. But i am a bit at a loss here on how to do this best, so any pointers would be greatly appriciated! — Rasmus, Mar 22 '14 at 09:34
Rasmus, what you say is: I have four ingredients and four tags. The Maximum of four and four is four, so I want four rows. In every row I want to Display the whole recipe. That makes no sense, as you can clearly see. To help yourself, make an example: write four ingredients and four tags and four result records. What do these records contain? Edit your request and show us. — Thorsten Kettner, Mar 22 '14 at 09:38
Why don't you use 2 queries, one to get the tags and another to get the ingredients? And then combine them as you like in the application. — ypercubeᵀᴹ, Mar 22 '14 at 09:40
Even more, according to what you ask, tags are related to ingredients? are tags independent of them? because if they're not related, there is no way you get the right results with what you propose. — Federico J., Mar 22 '14 at 10:15
Now that you've edited your request: In your tables ingredients and tags are related to the recipe but not to each other. In your sample there is a relation between ingredients and tags ("potato" belonging to "potato dish"). So something is wrong here. Either in your tables there *is* a relation between ingredients and tags and you forgot to use it in your select statement, or there is no relation and the dbms cannot find out why it should relate "potato" to "potato dish". — Thorsten Kettner, Mar 22 '14 at 10:24

Federico J. · Accepted Answer · 2014-03-22T10:50:25.910

Well, as soon as you're trying to retrieve 1 recipe, 4 tags and 4 ingredients, the question is: how do you want it? As you're going to 3 tables, and each has 4 and 4, logical is getting 16 rows: in each row, you'll have one propierty from what you look for.

Other question is if you wanted to get all of them in an only row, in that case, you'll have to group all of them in a field, if you wanted to retrieve it all at the same time:

I guess you have index tables like this:

recipe_has_tag - recipe_r_id, tag_t_id
ingredients_has_recipe - recipe_r_id, ingredient_in_id

So, what I'd do is retrieving all in a single row, concat all the fields with the name of tags and ingredients, and then in PHP explode them or show them directly, depending of what you wanted:

SELECT r.*, 
GROUP_CONCAT( t.name SEPARATOR ', ' ) , GROUP_CONCAT( i.name SEPARATOR ', ')
FROM recipe r

-- Get Tags from recipe
JOIN recipe_has_tag rht ON rht.recipe_r_id  = r.id
JOIN tag t ON rht.tag_t_id = t.id

-- Get Ingredients from recipe
JOIN ingredient_has_recipe ihr ON ihr.recipe_r_id = r.id
JOIN ingredients i ON ihr.ingredient_in_id = i.id

WHERE r.id = 1   
GROUP BY r.id

Other way would be to have different queries according to what you wanted to get, but as long as I don't know what you're going to do with the data, I can't propose anything else.

Take into account, that I'm guessing the name of the index tables and the joins, maybe you'll have to modify the fileds to have the right name

UPDATE

According to your comment, you probably have duplicated values in your tables. The best solution would be check all the values and make a normalized table, but you may do a workaround if you change

-- Change:
GROUP_CONCAT( t.name SEPARATOR ', ' ) , GROUP_CONCAT( i.name SEPARATOR ', ')
-- by 
GROUP_CONCAT( DISTINCT( t.name SEPARATOR ) ', ' ) , GROUP_CONCAT( DISTINCT( i.name ) SEPARATOR ', ')

Check the fiddle: http://sqlfiddle.com/#!2/626760/4

That works really well, the problem is, that the tags name and ingredient names are getting dublicated, so i get 16 tags and ingredient names where as i should only have had 4 :/ — Rasmus, Mar 22 '14 at 09:55
mmm, are you sure you haven't got duplicated values into the db? Anyway, you may group also by them and get only what you need — Federico J., Mar 22 '14 at 10:09
I added a fiddle to make the problem at bit eaasier to go by — Rasmus, Mar 22 '14 at 10:41
Check the new fiddle, it's done. You have to add `DISTINCT` and go — Federico J., Mar 22 '14 at 10:50

score 1 · Answer 2 · answered Mar 22 '14 at 10:53

1

From your SQL fiddle I take it that you don't want to select four rows, as you have been telling us all the while, but one record only. You are using group_concat and get duplicate strings. You can avoid this by using group_concat with DISTINCT:

SELECT r.*, 
GROUP_CONCAT(DISTINCT t.t_name SEPARATOR ', ' ) , GROUP_CONCAT(DISTINCT i.in_name SEPARATOR ', ')
FROM recipe r
JOIN recipe_has_tag rht ON rht.recipe_r_id  = r.r_id
JOIN tag t ON rht.tag_t_id = t.t_id
JOIN ingredient_has_recipe ihr ON ihr.recipe_r_id = r.r_id
JOIN ingredient i ON ihr.ingredient_in_id = i.in_id
WHERE r.r_id = 1   
GROUP BY r.r_id;

Here is the corrected SQL fiddle: http://sqlfiddle.com/#!2/626760/5.

answered Mar 22 '14 at 10:53

Thorsten Kettner

89,309
7
49
73

Right, sorry about that. my initial approach was to get a row per unique entry but Chococroc approach looked better to me, so i went with it. – Rasmus Mar 22 '14 at 11:03
Though, i would love to see how it could be done with the original 4 rows approach if you got the time for that – Rasmus Mar 22 '14 at 11:32
Rasmus: Ingredients and tags are not related! In your SQL fiddle: Would you combine ingredient "Gummiørn" with tag "tag1", "tag2", "tag3" or "tag4"? And why would you decide for *that* particular tag to be related to that ingredient? – Thorsten Kettner Mar 22 '14 at 12:12
So i need a relation between the tags and ingredients to get 1 specific ingredient to show up with 1 specific tag? – Rasmus Mar 22 '14 at 13:15
Yes. So far you have relations between recipes and tags with your bridge table recipe_has_tag. Of course you can have another table ingredient_has_tag. Are there some tags that are related to recipes and others to ingredients? Or are they even all related to ingredients? I imagine there to be tags for recipes like "soup" and others for ingredients like "contains lactose". However, think about how important it is for you to link tags also to ingredients. To have tags only per recipe are a much simpler model and thus easier to handle. – Thorsten Kettner Mar 22 '14 at 20:04
Considering this is only a small bit of my DB, would i need to have every table chained together in a giant spiderweb to be able to produce the specific quries that i am looking for or is there a better DB design so i wont have to have a FK to pretty much everything? – Rasmus Mar 23 '14 at 09:41

Too many results on a double join

2 Answers2