CODEWITHSUNDEEP
mysqljoinfield
user2600790
user2600790

Reputation: 1

Join 3 to 4 MySQL tables to display 2 fields

I'm lost and need help with a complex join of at least 3 tables.

I have 4 tables (recipes, recipes2tips, tips, and tips2recipes) with the following column names:

recipes = id, name, descrip, video

recipes2tips = id, recipe, tip

tips = id, name, descrip, video

tips2recipes = id, tip, recipe

I'm trying to display all tips that are related to each recipe WHERE the recipes.id will be the joining factor. The problem is that all of the tips ids are equal to the recipes2tips.tip and the tips2recipes.tip.

Here is my first attempt but it's missing something: PHP Code: [Select]

SELECT tips.id, tips.name, recipes2tips.recipe, recipes2tips.tip, recipes.id FROM tips, recipes2tips, recipes WHERE tips.id = recipes2tips.tip;

Any suggestions would be great.

Thanks

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269723

You are missing a join condition on your tables.

If you use proper join syntax (with an on clause), then you are much less likely to make this error:

SELECT tips.id, tips.name, recipes2tips.recipe, recipes2tips.tip, recipes.id
FROM tips join
     recipes2tips
     on tips.id = recipes2tips.tip
     recipes
     on recipes.id = recipes2tips.recipe ;

Upvotes: 2

Related Questions