1

These are our 3 tables. We want to a new recipe into the tables. The user gets a screen where they can fill in the RecipeName, the Method and they can select 3 ingredients from dropdown menu's. We are working with asp.net in visual studio with C#.

So when the user fills in every field the RecipeName and the Method will be added to the Recipes table. Then the ingredients which belong to the recipe have to be added to the IngredientRecipe table.

How to write this query, because we don't know the RecipeId since this autoincrements?

Recipes

RecipeId     RecipeName                Method
1            Bacon and Brocilli        Bake the bacon and cook the brocolli
2            Rice with chicken         Cook the rice and bake the chicken
4            Potato and Carrot         Cook the potatoes and the carrots
6            Rice Chicken and Carrot   Cook everything

Ingredients

IngredientId     IngredientName       IngredientCategorie
2                Bacon                3
3                Brocolli             2
4                Potato               1
5                Chicken              3
6                Carrot               2
7                Rice                 1

IngredientRecipe

RecipeUniqueID   RecipeId    IngredientId
1                   1         2
2                   1         3
3                   2         5
4                   2         7
5                   4         4
6                   4         6
7                   1         4
8                   2         6
9                   4         2
10                  6         7
11                  6         6
12                  5         5
Niek Nijland
  • 732
  • 1
  • 7
  • 20
  • 1
    how are you accessing your data? – whastupduck May 20 '13 at 03:51
  • Without knowing what you are using to connect to your database (and also, what DBMS you are using, although many might assume MS Sql Server) this is hard to answer! – joshuahealy May 20 '13 at 03:56
  • Also this sounds very homeworky.... If it's homework, then it should be tagged as such – joshuahealy May 20 '13 at 03:56
  • 2
    @appclay the [Homework tag is now deprecated](http://meta.stackexchange.com/questions/147100/the-homework-tag-is-now-officially-deprecated) – Adam Wenger May 20 '13 at 04:02
  • in most of the website they are providing us a facility to add recipe along with ingridients.but if u will first add a recipe and then add ingridients to this recipe make your process lengthy.i suggest you to add ingridients at the time of adding recipe..it will be nice. – Rahul May 20 '13 at 04:06
  • Ahh okay, sorry I didn't know about the homework tag being deprecated. I see people suggesting it so often that I thought it was a community standard... Again, sorry! – joshuahealy May 20 '13 at 04:39

4 Answers4

3

Save the recipe and ingredients first so they have valid ID's then save the association.

Larry McKenzie
  • 3,253
  • 25
  • 22
0

After adding the recipe, you should use SCOPE_IDENTITY or an OUTPUT clause to get the new RecipeID.

Reference: Best way to get identity of inserted row?

Community
  • 1
  • 1
jdl
  • 1,104
  • 8
  • 12
0

Assuming you are using SQL Server 2005 or later , you can use OUTPUT clause in INSERT statements. OUTPUT clause returns whatever info you required about rows that is inserted.

I think The OUTPUT clause is the safest mechanism in multi user databse insertion rather than using SCOPE_IDENTITY or IDENT_CURRENT See here on how to Use OUTPUT clause in INSERT statement.

Also see a very good explanation on advantage of OUTPUT clause over SCOPE_IDENTITY , @@IDENTITY on this Stackoverflow thread

Community
  • 1
  • 1
Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133
0

Try something like

call stored procedure to insert recipe

SP Code For insert recipe Start Insert into recipes values('','') select @Scope_Identity() // This will return you the last auto incremented value, which will be your recipe id SP Code for Insert Recipe End

now when you have the recipe id, you can pass it, to map recipe with ingredients.

Hope this helps

Qasim Javaid Khan
  • 650
  • 4
  • 10