problem with sql string

I am trying to return with count 0

I know that's not possible but how can I see them

This is a report that will tell me when there are no items showing on the partyguide page

selectcount(*)as reltheme, display_namefrom bb_guide_party

leftjoin(select bb_guide_party_seqfrom bb_guide_party_dept a, binbox_dept bwhere a.dept_id= b.dept_idand b.active_Flag= 1) dd

on bb_guide_party.bb_guide_party_seq= dd.bb_guide_party_seq

and bb_guide_party.active_flag=1

groupby display_name

havingcount(display_name)<1

Maybe you can something like this:

select * from (

selectcount(*)as reltheme, display_namefrom bb_guide_party

leftjoin(select bb_guide_party_seqfrom bb_guide_party_dept a, binbox_dept bwhere a.dept_id= b.dept_idand b.active_Flag= 1) dd

on bb_guide_party.bb_guide_party_seq= dd.bb_guide_party_seq

and bb_guide_party.active_flag=1

groupby display_name

) where reltheme = 0

Hope it helps

|||

the problem is that the count is always equal or greater then 1 because if a row shows up it is 1 count.

I need to check when it doesn't have a row.

|||

This is a total stab in the dark, since i'm not familiar with your data (which is the one, which is the many), but...

select a.bb_guide_party_seq, count(a.bb_guide_party_seq)
from bb_guide_party_dept a
inner join binbox_dept b
on a.dept_id = b.dept_id
left outer join bb_guide_party c
on c.bb_guide_party_seq = a.bb_guide_party_seq
where b.active_Flag = 1
and c.bb_guide_party_seq is null
group by a.bb_guide_party_seq

|||

selectdistinct dept_name

from bb_guide_party_dept a, binbox_dept bwhere a.dept_id= b.dept_id

and b.active_Flag= 1

selectdistinct dept_name

from bb_guide_party_dept a, binbox_dept bwhere a.dept_id= b.dept_id

I need to return what doesn't show up in the first select statement

|||

selectdistinct dept_name

from bb_guide_party_dept d, binbox_dept ewhere d.dept_id= e.dept_id

and e.active_Flag= 1

union

selectdistinct dept_name

from bb_guide_party_dept a, binbox_dept bwhere a.dept_id= b.dept_id

and e.dept_namenot the sameas b.dept_name