Reputation: 35
I'm pretty new to StackOverflow and never really needed to post but after some hours of reasearch I had to ask. I could maybe change the DB, but I'm trying to find a solution here.
So I have 2 Tables in my DB, [Pracas] and [Motoristas_Praca]
The View is not returning anything when [Motoristas_Praca] is empty, because of the //Motoristas_Praca.PracaID=Pracas.PracaID
Here is my View
SELECT Pracas.Zona,
SUM(CASE WHEN Motoristas_Praca.PracaID=Pracas.PracaID THEN 1 ELSE 0 END) AS 'Ocupantes'
FROM Motoristas_Praca, Pracas GROUP BY Pracas.Zona
The [Pracas] has 2 entries
I want the View to return,
Zona Ocupantes
-------- -------------
string1 0
string2 0
...when [Motoristas_Praca] is empty
Sorry for the bad post, but I need your help :)
Upvotes: 0
Views: 89
Reputation: 3660
You can use LEFT JOIN
, to get the desired output.
SELECT Pracas.Zona,
SUM(CASE WHEN Motoristas_Praca.PracaID IS NULL THEN 0 ELSE 1 END) AS 'Ocupantes'
FROM Pracas
LEFT JOIN Motoristas_Praca ON Motoristas_Praca.PracaID=Pracas.PracaID
GROUP BY Pracas.Zona
Hope this helps.
Upvotes: 1