1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

[SQL] Return 0 when table does not contain a row that references another table

Discussão em 'Outras Linguagens' iniciado por Stack, Setembro 10, 2021.

  1. Stack

    Stack Membro Participativo

    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:

    [​IMG]

    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?

    Continue reading...

Compartilhe esta Página