CODEWITHSUNDEEP
sql-servert-sqlcountreturn-value
Fay
Fay

Reputation: 1

Count returns 0 if no rows found

This is the code on sqlfiddle - http://sqlfiddle.com/#!6/9596d/8

create table tbla (uid nvarchar(10), name nvarchar(50));
create table tblb (uid nvarchar(10), cd nvarchar(5));

insert into tbla values ('abc','abraham lincoln');
insert into tbla values ('def','david copperfield');
insert into tblb values ('abc','a');
insert into tblb values ('abc','b');
insert into tblb values ('abc','c');
insert into tblb values ('def','a');
insert into tblb values ('def','c');

I need to return the count. And return 0 if no records found. This is what I have, but it doesn't return 0 for uid def

select a.uid, a.name, count(b.cd) as ct
from tblb b
left join
tbla a
on b.uid = a.uid
where b.cd = 'b'
group by a.uid, a.name

What am I doing wrong? It's using outer join.

Upvotes: 0

Views: 1516

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

IMHO this correct way of doing it,

create table #tbla (uid nvarchar(10), name nvarchar(50));
create table #tblb (uid nvarchar(10), cd nvarchar(5));

insert into  #tbla values ('abc','abraham lincoln');
insert into  #tbla values ('def','david copperfield');
insert into  #tblb values ('abc','a');
insert into  #tblb values ('abc','b');
insert into  #tblb values ('abc','c');
insert into  #tblb values ('def','a');
insert into  #tblb values ('def','c');

--select * from  #tblb
--select * from  #tbla
;With CTE as
(
select b.uid,  count(b.cd) as ct
from #tblb b
where b.cd = 'b'
group by b.uid
)
select a.uid,isnull(c.ct,0),a.name from #tbla a
left join cte c  on 
a.uid=c.uid 

drop table #tblb
drop table #tbla

Upvotes: 0

SqlZim
SqlZim

Reputation: 38043

You had your tables backwards for a left join, and you need to use the join clause instead of the where clause for conditions on the left join table. Otherwise you are turning your left join into an inner join.

select a.uid, a.name, count(b.cd) as ct
from tbla a
  left join tblb b
    on b.uid = a.uid
   and b.cd = 'b'
group by a.uid, a.name

rextester demo: http://rextester.com/ZXBS56241

returns:

+-----+-------------------+----+
| uid |       name        | ct |
+-----+-------------------+----+
| abc | abraham lincoln   |  1 |
| def | david copperfield |  0 |
+-----+-------------------+----+

Upvotes: 2

Related Questions