MySql query, table join

Question

I have two MySql tables, one of which contains food, and one contains recipes (product groups). The recipe table has a name, and then contains a product table containing all the food in this recipe. This table is connected to the kitchen table. Now I need to combine these tables and list them togheter.

Food table:

  foodid |  title |  calories
    1 banana 100
    2 apple 50

Recipe table:

  recipeid |  title 
    1 fruit-mix 

Receipe-element-table:

  receipeitemid |  recipeid |  foodid
    1 1 1
    2 1 2

I need to combine them into one list containing both food and recipes in the same list. Therefore, in the above example, I will need the following list:

  title |  foodid |  recipeid |  calories
 banana 1 null 100
 apple 2 null 50
 fruit-mix null 1 150 

Is it possible?

Answer 1

You are using a thing called UNION

The problem with joining is that all tables must have the same fields. But you can add fake fields in the SELECT query to get the result you want. And it can be slow with large tables.

SELECT title, foodid, NULL as recipeid, calories FROM Food-table UNION SELECT title, NULL as foodid, recipeid, calories FROM Recipe-table 

But you should really learn about JOINS , as there is a link table there.

Answer 2

 SELECT title, foodid, null, calories FROM Food UNION SELECT r.title, null, r.recipeid, sum(calories) FROM recipe r INNER JOIN RecipeItem ri ON ri.recipeid=r.recipeid INNER JOIN Food f ON f.foodid=ri.foodid GROUP BY r.title, r.recipeid