Complex SQL Server CE 4.0 SELECT query

Question

UPDATE: Rewrote the question to be more clear (hopefully)

I am using SQL Server CE 4.0 as a database.

I have 3 tables for this query:

• Artikels: holds the data concerning a certain Artikel(product).
• Bestellingen: Holds the general information of an order.
• BestelDetails: Holds the lines of an order.

The following query gives me a list of all artikels with relevant data to this report

Select a.id, a.code, a.naam, a.Voorraad
From Artikels a

This query gives me a smaller list of artikels and how many of them are still needed to fullfill the remaining (partial)orders.

Select a.id, sum (bd.Hoeveelheid)-sum(bd.Voldaan)-sum (bd.Geannuleerd)-sum(bd.BackOrder) as [Open]
From Artikels a
LEFT JOIN BestelDetails bd ON a.ID = bd.ArtikelID
LEFT JOIN Bestellingen b ON bd.BestelID = b.ID
WHERE b.Status = 2
GROUP BY a.id

Now I want to add a column to the first list with the result of [OPEN] where the ID is the same and if not, add a 0 instead.

If I run

Select a.Code, a.Naam, a.Voorraad, sum(bd.Hoeveelheid)-sum(bd.Voldaan)-sum(bd.Geannuleerd) as [Open]
From Artikels a
INNER JOIN BestelDetails bd ON a.ID = bd.ArtikelID
INNER JOIN Bestellingen b ON bd.BestelID = b.ID
WHERE b.Status = 2
GROUP BY a.Code, a.Naam, a.Voorraad

I get the smaller list with the extra info for that artikel from the first list. If I drop the WHERE clause and make LEFT JOINS, I get the data I need, but the calculation in the OPEN column is wrong.

Answer 1

Do you want to display all products but only sum those with status of 1? If so I think you need a subquery in your join. Try this:

Select a.code, a.Naam, a.Voorraad, isnull(sum(orders.Hoeveelheid)-sum(orders.Voldaan)-sum(orders.Geannuleerd),0) as Besteld

from Artikels a

left join 
    (   select bd.ArtikelID, bd.BestelID, bd.Hoeveelheid, bd.Voldaan, bd.Geannuleerd
        from BestelDetails bd
            join Bestellingen b on bd.BestelID = b.ID and b.status=1    ) orders on a.ID = orders.ArtikelID 

group by a.Code, a.Naam, a.Voorraad