CODEWITHSUNDEEP
sqlsql-server
Pedro Soares
Pedro Soares

Reputation: 35

Importance of WHERE in LEFT join

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

Answers (1)

Subin Chalil
Subin Chalil

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

Related Questions