How to get NULL or Zero values in SQL Server results set-CodePudding

Trying to get the output of all mentioned accounts(total of 9) even though there are no existing records for the GB.periode = 11

Tried using ISNULL(SUM(GB.bdr_val), 0) but still I'm only getting output for GB.reknr = 5210 OR GB.reknr = 5211 OR GB.reknr = 5250 OR GB.reknr = 5340.

SELECT 
    SUM(GB.bdr_val) AS Total_Material_M, 
    GR.oms25_0 AS Desc2, GB.reknr AS Account
FROM
    [100].[dbo].[gbkmut] GB 
INNER JOIN 
    [100].[dbo].[grtbk] GR ON GB.reknr = GR.reknr
WHERE
    GB.bkjrcode = 2021 
    AND GB.periode = 11 
    AND (GB.reknr = 5000 OR GB.reknr = 5050 OR GB.reknr = 5150 OR 
         GB.reknr = 5200 OR GB.reknr = 5210 OR GB.reknr = 5211 OR 
         GB.reknr = 5250 OR GB.reknr = 5340 OR GB.reknr = 5341)
GROUP BY 
    GR.oms25_0,GB.reknr
ORDER BY 
    GB.reknr ASC

I have a sample DB layout below,

CREATE TABLE gbkmut
(
    id INT,
    reknr INT,
    bdr_val INT,
    bkjrcode INT,
    periode INT
);

INSERT INTO gbkmut VALUES (1, 5210, 3511, 2021, 11);
INSERT INTO gbkmut VALUES (2, 5211, -40, 2021, 11);
INSERT INTO gbkmut VALUES (3, 5250, 33832, 2021, 11);
INSERT INTO gbkmut VALUES (3, 5340, -16, 2021, 11);

CREATE TABLE grtbk 
(
    id INT,
    reknr INT,
    oms25_0 varchar(60)
);

INSERT INTO grtbk VALUES (1, 5210, 'Description 1');
INSERT INTO grtbk VALUES (2, 5211, 'Description 2');
INSERT INTO grtbk VALUES (3, 5250, 'Description 3');
INSERT INTO grtbk VALUES (4, 5340, 'Description 4');
INSERT INTO grtbk VALUES (5, 5000, 'Description 5');
INSERT INTO grtbk VALUES (6, 5050, 'Description 6');
INSERT INTO grtbk VALUES (7, 5150, 'Description 7');
INSERT INTO grtbk VALUES (8, 5200, 'Description 8');
INSERT INTO grtbk VALUES (9, 5341, 'Description 9');

I still need the query output to show all the Total_Material_M, Desc2 and Account columns to show up even the values are 0 for specific bkjrcode and periode value combinations and SUMS, not sure what I'm doing wrong here.

CodePudding user response：

If I understand correctly, you are looking for OUTER JOIN, INNER JOIN will return rows that match between all conditions on two tables.

For this below query RIGHT JOIN will return rows based on [dbo].[grtbk] even conditions didn't match, But rows will be NULL when the row didn't match by the conditions GB.bdr_val, so we need to use ISNULL in the aggregate function.

SELECT 
    SUM(ISNULL(GB.bdr_val,0)) AS Total_Material_M, 
    GR.oms25_0 AS Desc2, 
    GR.reknr AS Account
FROM
    [dbo].[gbkmut] GB 
RIGHT JOIN 
    [dbo].[grtbk] GR ON GB.reknr = GR.reknr
    AND GB.bkjrcode = 2021 
    AND GB.periode = 11 
GROUP BY 
    GR.oms25_0, GR.reknr
ORDER BY 
    GR.reknr ASC

CodePudding user response：

You need to switch around the order of the joins and change it to a LEFT JOIN. You also need to move the conditions for GB into the ON clause.

SELECT 
    SUM(GB.bdr_val) AS Total_Material_M, 
    GR.oms25_0 AS Desc2, GB.reknr AS Account
FROM
    dbo.grtbk GR
LEFT JOIN 
    dbo.gbkmut GB ON GB.reknr = GR.reknr
    AND GB.bkjrcode = 2021 
    AND GB.periode = 11 
    AND (GB.reknr = 5000 OR GB.reknr = 5050 OR GB.reknr = 5150 OR 
         GB.reknr = 5200 OR GB.reknr = 5210 OR GB.reknr = 5211 OR 
         GB.reknr = 5250 OR GB.reknr = 5340 OR GB.reknr = 5341)
GROUP BY 
    GR.oms25_0,
    GB.reknr
ORDER BY 
    GB.reknr ASC;

db<>fiddle