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

Question

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 1

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!