COMBINE TWO QUERY IN SQL-CodePudding

I want to combine this two query

table 1:

Select fldCode,(COALESCE(sum(distinct fldIN),0)) -  (COALESCE(sum(distinct fldOut),0)) as totalStock from tblInventory group by fldCode

fldCode	totalStock
Humiseal	88
Silicon Rubber	54

table 2:

Select fldCode, fldSafetyStock from tblItems

fldCode	fldSafetyStock
Humiseal	165
Silicon Rubber	105

with the result like this table

RESULT TABLE:

fldCode	fldSafetyStock	totalStock
Humiseal	165	88
Silicon Rubber	105	54

CodePudding user response：

SELECT A.fldCode,A.totalStock,ITM.fldSafetyStock  FROM
(
   Select fldCode,
    (COALESCE(sum(distinct fldIN),0)) - (COALESCE(sum(distinct  fldOut),0)) as totalStock 
     from tblInventory group by fldCode
)AS A
JOIN tblItems AS ITM ON A.fldCode=ITM.fldCode

Could you please try this

CodePudding user response：

Assuming we can rely on both tables having all/the same items to report, we can use a join here:

SELECT t1.fldCode, t1.fldSafetyStock, COALESCE(t2.total, 0) AS totalStock
FROM tblItems t1
LEFT JOIN
(
    SELECT fldCode, SUM(DISTINCT fldIN) - SUM(DISTINCT fldOut) AS total
    FROM tblInventory
    GROUP BY fldCode
) t2
    ON t2.fldCode = t1.fldCode;

The above answer assumes that every fldCode which you want to report can be found in the tblItems table. Items may be out of inventory, and the report would just list zero for the total stock in that case.