MySql query, combine tables

mysql

I have two MySql tables, one containing food and one containing recipes (groups of foods). The recipe-table have a title and then has a item-table containing all food in that recipe. That table is linked to the food-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-item-table:

receipeitemid | recipeid | foodid
   1               1         1
   2               1         2

I need to combine them to one list containing both food and recipes in same list. So in the example above I would need a list like this:

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

Is this in anyway possible?

Best Solution

You use a thing called UNION

The issue with union is that all tables must have the same fields. But you can add fake fields in the SELECT query to get the result you need. 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 look into JOINS, since you have a link table there.