MySql query, combine tables

Question

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?

Answer 1

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.

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

Answer 3

Thanks!

I think the union-thing was what I was looking for. My example was a little bit simplified from the real thing I was doing. Thanks again!

Answer 4

Just one more question, now that I have done my union I have in both querys a table tbluser. I use GROUP BY tbluser.userid in both queries and the queries get grouped one by one. But I still got two rows as the unioin doesn't group. Can I group my union result?

My query (a little bit simplified):

SELECT
SUM(tblfooditem.calories)
FROM tblfooditem
INNER JOIN tbluser ON tblfooditem.userid = tbluser.userid
WHERE tblfooditem.userid=?userid AND tblfooditem.date=?date
GROUP BY tbluser.userid
UNION
SELECT
SUM(tbladdedmealitem.calories)
FROM tbladdedmeal
INNER JOIN tbladdedmealitem ON tbladdedmeal.addedmealid = tbladdedmealitem.addedmealid
INNER JOIN tbluser ON tbladdedmeal.userid = tbluser.userid
WHERE tbladdedmeal.userid=?userid AND tbladdedmeal.date=?date
GROUP BY tbluser.userid;