Good afternoon, I have two tables with the same structure and I would like to subtract one from the column (quantity) but I am not getting it, it follows as tables.
|idProduct|amount|classification|
|---------|------|--------------|
|1|1|A|
|1|18|AA|
|idProduct|amount|classification|
|---------|------|--------------|
|1|1| A|
|1|16|AA|
What I have tried:
I try:
SELECT p.[idProduct], ((IsNull(p.[amount], 0)) - IsNull(s.[amount], 0)) AS inStock, p.classification FROM table1 AS p
LEFT JOIN table2 AS s ON p.[idProduct] = s.[idProduct]
GROUP BY p.[idProduct], p.[classification], p.[amount], s.[amount]
|idProduct|amount|classification|
|---------|------|--------------|
|1|0|A|
|1|-15|A|
|1|17|AA|
|1|2|AA|
only the first and last lines are correct, but I don’t know how to remove the others