Subtraction between two SQL tables

**KADRI** · 08-12-2009

I search the net and no answers or a few that I find, but unfortunately not good at all. I explain my problem,
Table 1 => mark, libel, model, series, qty

Table 2 => mark, libel, model, series, qty

to display the number of product group is followed by model:

Code:

SELECT make, model, libel, series, SUM (qty) AS qty 
FROM TABLE 1 
GROUP BY model;

and what I want to do is subtraction of my table 1 with my table 2. Basically if I find the same product in my table 2, I want in my table 1 it is automatically removed. I hope that my explanation was clear and optimum.

**DotNetUser** · 08-12-2009

I hope that the tables do not actually have these names!
- TABLE is an SQL reserved word.
- A table name with a space is a source of misunderstanding and error for the DBMS.

Turning now to the problem ...

what I want to do is subtraction of my table 1 with my table 2. Basically if I find the same product in my table 2, I want in my table 1 it is automatically removed.

Would you say:
1) What you want to also count how many vehicles of each model in Table 2 can do arithmetic subtraction of two numbers for each model?
2) What you want to remove from the table 1 the rows for the existing models in Table 1?

**KADRI** · 08-12-2009

SELECT DISTINCT model, SUM (quantity), SUM (quantity) FROM pret_sav INNER JOIN stock_reel_sav etc...

But alas, like I always got stuck I do not even know if it was the right solution.

**DotNetUser** · 08-12-2009

Some modeling problems in your database!

If "series" is the serial number of a device, how can you have a quantity of 4 with the same serial number? Normally the serial numbers are unique for a device model! Then you have the same columns in your two tables for the same devices. Then you have redundancy of data. I doubt from the name of your table that you registered in the table 'pret_sav' material contained in table 'stock_reel_sav'. That equipment can be lent or not. Why not simply have added a boolean column to the table 'stock_reel_sav' Whether the material is taken (or in this stock which is the same but with a reversed).

If it is not too late, I advise you to review your data model.

Returning to your needs ...

Since I returned to my table 'pret_sav' the same model (SGHC140)

I want in my table 'stock_reel_sav' displays 3 quantity.

In other words, you want to update the quantity for each model according to what is # lend?