Select part of a SELECT query FROM another table

[savedlema]

Hi all! I don't know if that was a good title, but;

I have this query (I will call it query1):

"SELECT InvID, quantity FROM Recipe WHERE MenuID = 1"

In my tables, this will give me something like Water(20), Oil(30)

Now, from this result of a SELECT query1 (i.e Water(20), Oil(30) or whatever) I want to take just Water,Oil.. (leave out the quantities) and then SELECT these InvID (water,oil..) from another table called "Inventory", but pull them with their quantity in that Inventory table.(i.e if the quantity of Water in Inventory table is 1000, pull Water (1000))

The goal is to subtract the quantities in the Inventory table with the quantities of the 1 select query (which returned 20,30...) (to get Water (1000) - Water (20) possible)

I'm making an application with VB.NET & Mysql and I'm stuck in this part. Can someone please help?

[gusano79]

If I understand you correctly, this should be workable in a single query. Before we get into details, how familiar are you with MySQL JOIN syntax? ...and table aliases in particular?

[savedlema]

I have worked with mysql INNER JOIN in the past projects, like this one:

"SELECT tblDetails.StudentID,tblDetails.FirstName, tblDetails.Surname," _
                    + "tblBiology1.Test1, tblBiology1.Test2,tblBiology1.Grade " _
                    + "FROM tblDetails INNER JOIN tblBiology1 ON " _
                    + "tblBiology1.StudentID = tblDetails.StudentID " _
                    + "WHERE tblBiology1.StudentID = '" & Trim(Me.txtSearch.Text) & "'"

By table aliases, do you mean something like SELECT customer_first_name AS name?

Do I need to elaborate my question more? You can please look at the snapshot of my tables structure (but don't mind about the Menu table). The table inventory contains the total available raw materials/recipes. Query1 shows what and what recipes have been consumed, now I need to update the Inventory table.

I appreciate.

[pritaeas]

You can join both tables on InvID:

SELECT R.InvID, R.Quantity, I.Amount, (I.Amount - R.Quantity) AS Difference
FROM Recipe R, Inventory I
WHERE R.MenuID = 1
AND R.InvID = I.InvID

[savedlema]

pritaeas, Thank you so much!

Now I'm able to get the amount that remains after the subtraction, Now, how do I update individual inventory items Quantities in the inventory table to reflect these changes there? Because up to now, the inventory table is unchanged (Quantities remains the same)

I kept the result of your SELECT into a dataset (and I was able to view it ina gridview)

[savedlema]

HI Prataeas!

I kept trying, finally I have managed to accomplish what I want with this code. Please see if there are any risks or if there is a better way of doing it.

 SQL = "CREATE TABLE difference AS SELECT R.InvID, R.Quantity, I.Amount, (I.Amount - R.Quantity) AS Difference " _
            & "FROM Recipe R, Inventory I " _
            & "WHERE(R.MenuID = 1) " _
            & "AND R.InvID = I.InvID; " _
            & "UPDATE Inventory SET Amount = (SELECT Difference FROM difference WHERE Difference.InvID = Inventory.InvID)"

        cmd.Connection = con
        cmd.CommandText = SQL
        da.SelectCommand = cmd

        cmd.ExecuteNonQuery()

        MsgBox("QUERRY  PERFORMED")
        con.Close()

Lastly, I have spent about a week trying to solve this! So I really want to thank you prateaus for stopping by and helping.

Thank you too gusano, your support is immeasurable.

And, I wonder how did you guys learn all this.

[pritaeas]

You might be able to do this in a single UPDATE statement, but if this works, use it.

[savedlema]

Single UPDATE? Can you give the statement please?

[pritaeas]

I cannot test without recreating some of you data, so I'll leave that as a challenge for you ;)

[savedlema]

Cool.

Thank you very much.

I can now mark the thread solved.