oracle left join查询问题,麻烦高手进来看看
两张表如下：
一.论坛版块表:bbsInfo,数据如下
  BBSCAPTIONID BBSCAPTION BBSNAMEID 
1 C001 资讯区 BN001 
2 C001 资讯区 BN002  
3 C001 资讯区 BN003  
4 C002 休闲区 BN004 
5 C002 休闲区 BN005  
6 C002 休闲区 BN006 
7 C002 休闲区 BN007  
8 C002 休闲区 BN008 
9 C003 管理区 BN009  
10 C004 求助区 BN010  

二.论坛帖子表:bbsCardInfo,数据如下
  BBSCAPTIONID BBSNAMEID ROLEID CARDID CARDSTATE 
1 C001 BN001 10001 Z10001 3  
2 C001 BN002 10001 Z10002 3  
3 C001 BN003 10001 Z10003 3  
4 C002 BN004 10002 Z10004 3  
5 C003 BN009 10002 Z10005 3  
6 C001 BN002 10001 Z10006 1  

CARDSTATE：3表示精华帖,1为普通

select a.bbscaptionid,a.bbsnameid,count(b.bbscaptionid) from Bbsinfo a left join Bbscardinfo b 
on a.bbsnameid = b.bbsnameid  
group by a.bbscaptionid,a.bbsnameid
这样能查询到全部版块的数据，就是全部的帖子数,版块没有帖子则会自动补0(left join)

但我要查询所有版块的精华帖子数时却查询不出来了,就加了个where条件,就只显示CARDSTATE为3的版块了,闷,试了各种方法都解决不鸟,请高手解决.代码如下
select a.bbscaptionid,a.bbsnameid,count(b.bbscaptionid) from Bbsinfo a left join Bbscardinfo b 
on a.bbsCaptionId = b.bbsnameid where b.cardstate = 3  
group by a.bbscaptionid,a.bbsnameid;

------解决方案--------------------

探讨

SQL codeselect a.bbscaptionid
, a.bbsnameid
, count(b.bbscaptionid)
from Bbsinfo a,Bbscardinfo b
where a.bbsCaptionId = b.bbsCaptionId(+)
and a.bbsnameid=b.bbsnameid(+)
and b.cardstate(+) = 3
group by
a.bbscaptionid
, a.bbsnameid;