Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Posted by Simon Morris
Simon Morris
16,250 Points

How do i combine 2 tables in SQL and list them in a select input box

im in the process of building a recipe management database i have 3 tables

dishes recipes ingredients

i have also created relationship tables recipe_dishes recipe_ingredients

basically dishes can have both an ingredient and recipes. i would like to know how i would create a select statement that will list out both ingredients and recipes with their id and name so that i can them add them to the recipe_dishes table if either an ingredient or a recipe is selected

4 Answers

kevin revill
kevin revill
7,533 Points
kevin revill
kevin revill
7,533 Points

easy answer :) you can use sub selects :) Which are realllyyyy slow when the data builds up or use temporary tables. Which is a lot faster.

A temporary table is a table that only exists for that user for that instance. ie its created when the script starts and once the date is on screen and the script ends it auto deletes ;)

So what you do is this

CREATE TEMPORARY TABLE mydata SELECT blah blah blah [first query];

INSERT into mydata SELECT blah blah blah [second query];

Now grab the data. We can use group by to grab the unique rows. select * from mydata group by id;

The other option is that you can do the following which when you get big data levels is slower.

SELECT z.id,z.name FROM ( SELECT blah blah blah [first query] UNION SELECT blah blah blah [second query] } as z GROUP BY z.id

Of course replace <first query> and <second query> with the two queries you mentioned.

Simon Morris
Simon Morris
16,250 Points

Ahh i see that works!, is there a way i can the create a third column (type) so that i can determine which row is from which table

Simon Morris
Simon Morris
16,250 Points

DROP TABLE dish_allergens; CREATE TEMPORARY TABLE dish_allergens (SELECT DISTINCT rd.dish_id as dish_id, a.code, a.name FROM allergens a INNER JOIN ingredient_allergens ia ON ia.allergen_id = a.id INNER JOIN ingredients i ON ia.ing_id = i.id INNER JOIN recipe_ingredients ri ON i.id = ri.ing_id INNER JOIN recipes r ON ri.recipe_id = r.id INNER JOIN recipe_dishes rd ON (rd.recipe_id = r.id) JOIN dishes d ON d.id = rd.dish_id);

INSERT INTO dish_allergens (SELECT rd.dish_id as dish_id, a.code, a.name FROM allergens a INNER JOIN ingredient_allergens ia ON ia.allergen_id = a.id INNER JOIN ingredients i ON i.id = ia.ing_id INNER JOIN recipe_dishes rd ON rd.ing_id = i.id);

SELECT DISTINCT code, name FROM dish_allergens WHERE dish_id = 5 ORDER BY code;

Would this be the correct way about getting all the allergen information for a particular dish?

do you have to do anything different in order for it to work on php here is my function in php

it doesn't seem to be working though

function data_dish_allergens($dbc, $id){

$q = "DROP TABLE dish_allergens;
    CREATE TEMPORARY TABLE dish_allergens (SELECT DISTINCT rd.dish_id AS dish_id, a.code, a.name 
    FROM allergens a
    INNER JOIN ingredient_allergens ia
    ON ia.allergen_id = a.id
    INNER JOIN ingredients i
    ON ia.ing_id = i.id
    INNER JOIN recipe_ingredients ri
    ON i.id = ri.ing_id
    INNER JOIN recipes r
    ON ri.recipe_id = r.id
    INNER JOIN recipe_dishes rd
    ON (rd.recipe_id = r.id)
    JOIN dishes d
    ON d.id = rd.dish_id);

    INSERT INTO dish_allergens (SELECT rd.dish_id AS dish_id, a.code, a.name 
    FROM allergens a
    INNER JOIN ingredient_allergens ia
    ON ia.allergen_id = a.id
    INNER JOIN ingredients i
    ON i.id = ia.ing_id
    INNER JOIN recipe_dishes rd
    ON rd.ing_id = i.id);

    SELECT DISTINCT code, name 
    FROM dish_allergens 
    WHERE dish_id = $id
    ORDER BY code;";

$r = mysqli_query($dbc, $q);



echo '<ul class="list-inline">';
while($data = mysqli_fetch_assoc($r)){
    echo '<li data-toggle="tooltip" data-placement="top" title="'.$data['name'].'">'.$data['code'].'</li>';
}

echo '</ul>';

}

kevin revill
kevin revill
7,533 Points
kevin revill
kevin revill
7,533 Points

What is the relationship? is it a dish has one to many recipes and recipes have one to many ingredients? Do i assume you want a dish name, recipe title and description and ingrediant name,amount? I need a little better understanding. Then I can through you a query together.

Simon Morris
Simon Morris
16,250 Points

dishes have a many to many relationship with recipes, recipes have also many to many with ingredients, i need the ingredient id and name and the same with recipes.

i want to create a select box that has a list of all ingredients and recipes so that i can add them to a dish if you understand where i am coming from?

kevin revill
kevin revill
7,533 Points
kevin revill
kevin revill
7,533 Points

So in this case we actually dont need to worry about dishes as you are adding the recipes to a dish.

table structure of the two tables we are interested in

recipes id - int primary key name - varchar Possible other fields...

ingredients id - int primary key name - varchar

The problem you have is that a recipe is a single record and so is an ingredient. You thus need an additional table to store the relationship. because a recipe has many ingredients.

new table recipe_ingredients id - int primary key (because you always should carry an id column) recipes_id - int - this works as a forieng key to identify for the recipe ingredients_id - int - this works a s a foriegn key to identify the ingredient. other fields like amount to use ;)

So lets pretend that we have the following recipes

recipes id:1 - name: steak pie id:2 - name: apple pie

we use the following ingredients

ingredients ```id:1 - name: flour id:3 - name: butter id:4 - name: apples id:5 - name: steak id:6 - name: water id:7 - name: sugar

Now we say that pie crust is made up of flour,water and butter and that the difference is sugar in the pastry for the apple pie and of course the filling.

So our joining table is:
recipe_ingredients
```id:1 - recipes_id: 1 - ingredients_id: 1
id:1 - recipes_id: 1 - ingredients_id: 2
id:1 - recipes_id: 1 - ingredients_id: 5
id:1 - recipes_id: 1 - ingredients_id: 6

id:2 - recipes_id: 1 - ingredients_id: 1
id:2 - recipes_id: 1 - ingredients_id: 2
id:2 - recipes_id: 1 - ingredients_id: 5
id:2 - recipes_id: 1 - ingredients_id: 6
id:2 - recipes_id: 1 - ingredients_id: 1
id:2 - recipes_id: 1 - ingredients_id: 7

As you can see we have the id's of all the ingredients to all the recipies in this table. if you change a recipe name or an ingredient name, because we are running from the id's it will not effect you. This is called normalisation. So now to the clever bit. I call it kevins guide to sql ;)

An sql statement is made up of 3 things. What you want, the tables the data is in and the limitations

The first job i do is label the tables. recipes = r, ingredients = i, recipes_ingredients = ri

Now i can see that you are askig for 2 things. 1) to be able to get a list of ingredients based on a recipe 2) to be able to grab a list of recipes based on ingredients. I will assume you will provide these as two different dropdowns.

so lets do 1) First lets ask for what we want. using the labels we decided above

SELECT i.id,i.name

Now we tell it the tables...

FROM ingredients as i INNER JOIN recipe_ingredients as ri ON ri.ingredients_id = i.id INNER JOIN recipes as r ON ri.recipes_id = r.id

Now this would give us everything out of the database, but we want it for a specific recipe. This is our limitation.

WHERE r.id = '1'

In this case we have chosen recipe 1. Which is stake pie.

So all together the query is....

SELECT i.id,i.name FROM ingredients as i INNER JOIN recipe_ingredients as ri ON ri.ingredients_id = i.id INNER JOIN recipes as r ON ri.recipes_id = r.id WHERE r.id = '1'

Item 2) This is nearly the same. as in we are using the same tables, so we have the joins we just need to change the limitation and what we are asking for.

SELECT r.id,r.name FROM ingredients as i INNER JOIN recipe_ingredients as ri ON ri.ingredients_id = i.id INNER JOIN recipes as r ON ri.recipes_id = r.id WHERE i.id = '4'

This time we are grabing a list of recipes, and because ingredient 4 is apples. we will of course return apple pie.

Hope that helps :)

Finally you can in the what am i aksing for, relabel if you wish. SO above you coudl have asked SELECT r.id as the_recipe_id,r.name as the_recipe_name this is useful if you are returning data from connecting multiple tables and what you are after happens to have the same column name.

kevin revill
kevin revill
7,533 Points
kevin revill
kevin revill
7,533 Points

Apologise i cannot seem to format this. when looking at thew sql. copy it into notepad and every time i capitalise a word. put it on a new line. makes it easier to read.

Simon Morris
Simon Morris
16,250 Points

Kevin do you have an email in which we could talk on? i will send you a entity relationship diagram of my tables and then maybe you could see what i'm trying to accomplish, i find it very hard to get my point across through words.

basically i have many tables

allergens ingredient_allergens ingredients ingredient_recipes recipes recipe_dishes dishes

all of these tables have id's as primary keys

for the recipe_dishes table

it has 3 foreign keys

recipe_id ing_id dish_id

what i want is to have a dish that can have both a recipe and a single ingredient.

thats all fine and dandy as it works. but i would like to know how i would gather all allergen information for a dish

i can do it separately with joins

so for allergen info for the recipes that are related to the dish would be

SELECT DISTINCT a.code, a.name FROM allergens a JOIN ingredient_allergens ia ON a.id = ia.allergen_id LEFT JOIN ingredients i ON ia.ing_id = i.id INNER JOIN recipe_ingredients ri ON i.id = ri.ing_id LEFT JOIN recipes r ON ri.recipe_id = r.id INNER JOIN recipe_dishes rd ON r.id = rd.recipe_id WHERE rd.dish_id = 5 ORDER BY a.code

and then for ingredients

SELECT DISTINCT a.code, a.name FROM allergens a INNER JOIN ingredient_allergens ia ON ia.allergen_id = a.id
LEFT JOIN ingredients i ON ia.ing_id = i.id INNER JOIN recipe_dishes rd ON i.id = rd.ing_id WHERE rd.dish_id = 5 ORDER BY a.code

i would like to try get this into one query so that i don't get duplicate results for the allergen information.