Daryl Daryl - 1 month ago 15
SQL Question

MS SQL Query (How to Subtract to two columns in different tables)

Hi i have two tables which is tblPOdetails and tblPDdetails

tblPOdetails

PONumber - Item - Qty - Price
12345---------soap------5-------4.50


tblPDdetails

PONumber - Item - Qty - Price
12345---------soap------4-------4.50


Result I Want

PONumber - Item - Qty - Price
12345---------soap------1-------4.50


all the columns and fields in that table is same.

Now i want to subtract tblpodetails.qty - tblpddetails.qty where PONumber = '12345' i want to get the result of that scenario can anyone teach me the script.

Thank you!

Answer

If PONumber is your table key in both tables, this query should give you your required result:

SELECT tblPOdetails.PONumber AS PONumber,
       tblPOdetails.Item AS Item,
       (tblPOdetails.Qty - tblPDdetails.Qty) AS Qty,
       tblPOdetails.Price AS Price
    FROM tblPOdetails
    JOIN tblPDdetails ON tblPOdetails.PONumber = tblPDdetails.PONumber
    WHERE tblPOdetails.PONumber = 12345

Edit: Added table name to PONumber.

Edit2: Replaced USING with ON. Thanks for the tip Hart CO!