Return 0 when table does not contain a row that references another table-CodePudding

I have two tables, Kurv and Kurvtilmeldingsvalg. Each row in Kurvtilmeldingsvalg contains a foreign key to a row in Kurv. However, when the Kurv is first created, no rows in Kurvtilmeldingsvalg exist yet.

I have a script that calculates a number based on some values in Kurvtilmeldingsvalg. However, if a row in this table does not yet exist, then the calculation should return 0.

Currently I am using CASE WHEN conditions to calculate the correct sum. However, this does not take non-existing rows in the Kurvtilmeldingsvalg table into account.

SELECT Kurv.Id AS KurvID,
       ISNULL(SUM(CASE
                      WHEN KurvTilmeldingsvalg.Antal - KurvTilmeldingsvalg.OriginalAntalSolgte < 0 THEN 0
                      ELSE (KurvTilmeldingsvalg.Antal - KurvTilmeldingsvalg.OriginalAntalSolgte)
                  END * nc_valglinjeBase.nc_deltageroptaelling), 1) AS TotalAntal
FROM Kurv
LEFT JOIN KurvTilmeldingsvalg ON Kurv.Id = KurvTilmeldingsvalg.KurvId
LEFT JOIN nc_valglinjeBase ON KurvTilmeldingsvalg.Valglinje = nc_valglinjeBase.nc_valglinjeId
WHERE Kurv.Status in (0,
                      1,
                      2,
                      13/*,14*/)
GROUP BY Kurv.Id
ORDER BY TotalAntal DESC

I attempted a solution by doing a subquery inside the WHEN clause:

WHEN (Select Count(*) FROM Kurvtilmeldingsvalg INNER JOIN Kurv ON Kurvtilmeldingsvalg.KurvId = Kurv.Id) = 0 THEN 0

However i get an error with the following message: "cannot perform an aggregate function on an expression containing an aggregate or a subquery".

Current incorrect output:

Desired output would return 0 instead of 1. The Kurv in this example is not referenced by any rows in Kurvtilmeldingsvalg.

How can i return 0 when there are no rows in the Kurvtilmeldingsvalg table that reference a row in Kurv?

CodePudding user response：

i did this super-fast and filling in the blanks the best i can based on what i think you're trying to do.

create table kurv (id int, [status] int)
create table kurvtilmeldingsvalg (kurvid int, antal int, originalantalsolgte int, valglinje int)
create table nc_valglinjebase (nc_valglinjeid int, nc_deltageroptaelling int)

insert into kurv values (1,1)
insert into kurv values (2,0)
insert into kurv values (3,1)
insert into kurv values (4,2)
insert into kurv values (5,13)

insert into kurvtilmeldingsvalg values (1,29,13,101)
insert into kurvtilmeldingsvalg values (2,9,3,101)
insert into kurvtilmeldingsvalg values (3,2,1,102)
insert into kurvtilmeldingsvalg values (3,44,20,102)
insert into kurvtilmeldingsvalg values (2,30,11,103)

insert into nc_valglinjebase values (101,3)
insert into nc_valglinjebase values (102,8)
insert into nc_valglinjebase values (103,5)

select a.id as kurvid
    , case
        when not exists (select 1 from kurvtilmeldingsvalg d where a.id = d.kurvid) then 0
        else isnull(sum(case when b.antal - b.originalantalsolgte < 0 then 0 else b.antal - b.originalantalsolgte end * c.nc_deltageroptaelling), 1)
    end as totalantal
from kurv a
    left join kurvtilmeldingsvalg b on a.id = b.kurvid
    left join nc_valglinjebase c on b.valglinje = c.nc_valglinjeid
where a.status in (0,1,2,13)
group by a.id
order by totalantal desc

drop table kurv
drop table kurvtilmeldingsvalg
drop table nc_valglinjebase

tldr; i just used EXISTS to check to see if there are matching rows before checking for NULLs.

it yields this:

kurvid      totalantal
----------- -----------
3           200
2           113
1           48
4           0
5           0

id 4 and 5 do not have any matching rows in the kurvtilmeldingsvalg table so they result to 0 instead of 1