compare tables, show/calculate differences 1

[jozino01]

hi,
i have two tables table1 (production) and table2 (sales)

table1:
product quantity
A 10
B 15
C 10

table2:
product quantity
A 10
B 10

i want to compare these two tables and get the result:
product quantity
B 5
C 10

this result would represent inventory (production minus sales)

can it be done in one query, please?

 

[cranebill]

SELECT Table1.qty, Table2.sales, Table1.qty-Table2.sales as availible
FROM Table1 INNER JOIN Table2 ON Table1.test1 = Table2.test1;

Here is an example... you will have to modify with your table/field names.

 

[cranebill]

SELECT production.quantity, sales.quantity, production.quantity-sales.quantity as availible_qty
FROM production INNER JOIN sales ON production.product = sales.product;

Didnt realize you had listed your names

 

[cranebill]

SELECT production.product,production.quantity as prod_qty, sales.quantity as sales_qty, production.quantity-sales.quantity as availible_qty
FROM production INNER JOIN sales ON production.product = sales.product;

breaks it out better for ya.

 

[PHV]

SELECT P.product, P.quantity-Nz(S.quantity,0) AS quantity_left
FROM production AS P LEFT JOIN sales AS S ON P.product = S.product

You may add the following criteria:
WHERE P.quantity <> Nz(S.quantity,0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jozino01]

thanks to both of you.
i tried all your suggestions and it looks like the left join does the work

SELECT TABLE1.amount, TABLE2.amount, TABLE1.AMOUNT-TABLE2.AMOUNT AS INVENTORY
FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.product = TABLE2.product;

TABLE1.AMOUNT TABLE2.AMOUNT INVENTORY
10 10 0
15 10 5

----------------------------------------------------

SELECT TABLE1.PRODUCT, TABLE1.AMOUNT, TABLE2.AMOUNT, TABLE1.AMOUNT-TABLE2.AMOUNT AS INVENTORY
FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.PRODUCT=TABLE2.PRODUCT;

PRODUCT TABLE1.AMOUNT TABLE2.AMOUNT INVENTORY
A 10 10 0
B 15 10 5

-----------------------------------------------------

SELECT TABLE1.PRODUCT, TABLE1.AMOUNT-NZ(TABLE2.AMOUNT,0) AS AMOUNT_LEFT
FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.PRODUCT = TABLE2.PRODUCT
WHERE TABLE1.AMOUNT <>NZ(TABLE2.AMOUNT,0);

PRODUCT INVENTORY
B 5
C 10

 

[lespaul]

Do you know WHY the left join worked?

See Understanding SQL Joins link below.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design
Understanding SQL Joins