CODEWITHSUNDEEP
mysql
Marty
Marty

Reputation:

MySql query, combine tables

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?

Upvotes: 4

Views: 6892

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415765

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

Upvotes: 4

Ólafur Waage
Ólafur Waage

Reputation: 70001

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.

Upvotes: 6

Related Questions