count(*) doesn’t return 0

i have a sql problem ,please help me this is my query

  select count(category_value .list_value_id) as jobs , category_type.value as category
from list_values category_type
full outer join params category_value
on category_type.list_value_id = category_value .list_value_id
join qrtz_triggers jobs
on category_value .object_id = jobs.job_name
and jobs.trigger_state ='PAUSED'
and category_value.attr_id = 9158075153713931109
where category_type.attr_type_def_id = 9158075154713931109
group by category_type.value;

attr_id is list value which contains 9158075158713931109(non-critical) and 9158075157713931109(critical)

returns result:
JOBS,   CATEGORY
2        Non-Critical


expected result is :
JOBS,   CATEGORY
2        Non-Critical
0          Critical

list_values table conains
list_value_id          values
9158075158713931109     non-critical
9158075157713931109      critical


params table
list_value_id          attr_id                         object_id
9158075158713931109     9158075153713931109           a
9158075157713931109      9158075153713931109          b
9158075157713931109      9158075153713931109          c
9158075158713931109     9158075153713931109           d

qtz_trigger table  i need triggers with state "paused"

job_name            trigger_state 
b                     paused
a                     paused
e                     normal
c                     paused

I even tried for category_value .object_id in (select jobs.job_name from qrtz_triggers jobs where jobs.trigger_type =’SIMPLE’) but getting error command not properly ended

Answer

I think you actually want left joins, starting from the list_values table and then brining params and qrtz_triggers:

select count(t.job_name) as jobs, v.value as category
from list_values v
left join params p
    on  p.list_value_id = v.list_value_id
    and p.attr_id       = v.attr_type_def_id
left join qrtz_triggers t
    on  t.job_name      = p.object_id
    and t.trigger_state = 'PAUSED'
where v.attr_type_def_id = 9158075154713931109
group by v.value;

The conditions were a bit messy in the originaly query so I tried to re-arrange that – you might need to review that. I also used short and representative table aliases.