4

My code from SQL Server:

SELECT ESTAGIO.SK_ESTAGIO, ISNULL(count(ESTAGIO.SK_ESTAGIO), 0) as how_many
 from ESTAGIO
 left join ESTAGIARIO
 on ESTAGIARIO.SK_ESTAGIO = ESTAGIO.SK_ESTAGIO
 group by
    ESTAGIO.SK_ESTAGIO

When "ESTAGIO.SK_ESTAGIO" doesn't exist in the table "ESTAGIARIO" it returns 1 instead of 0, I already tried to use ISNULL(), NULLIF() and COALESCE() and still couldn't find the problem that is making the query above returning 1 when it should be 0.

Leandro Batista
  • 57
  • 1
  • 8

1 Answers1

7

You are counting the wrong field. Do it like this, taking the field from the outer joined table ESTAGIARIO (not from ESTAGIO):

SELECT ESTAGIO.SK_ESTAGIO, Count(ESTAGIARIO.SK_ESTAGIO) as how_many
 from ESTAGIO
 left join ESTAGIARIO
 on ESTAGIARIO.SK_ESTAGIO = ESTAGIO.SK_ESTAGIO
 group by
    ESTAGIO.SK_ESTAGIO

BTW, count can never return null.

trincot
  • 317,000
  • 35
  • 244
  • 286