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;

​x
 
  select count(category_value .list_value_id) as jobs , category_type.value as categoryfrom list_values category_typefull outer join params category_valueon category_type.list_value_id = category_value .list_value_idjoin qrtz_triggers jobson category_value .object_id = jobs.job_nameand jobs.trigger_state ='PAUSED'and category_value.attr_id = 9158075153713931109where category_type.attr_type_def_id = 9158075154713931109group 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

 
returns result:JOBS,   CATEGORY2        Non-Critical​​expected result is :JOBS,   CATEGORY2        Non-Critical0          Critical​list_values table conainslist_value_id          values9158075158713931109     non-critical9158075157713931109      critical​​params tablelist_value_id          attr_id                         object_id9158075158713931109     9158075153713931109           a9158075157713931109      9158075153713931109          b9158075157713931109      9158075153713931109          c9158075158713931109     9158075153713931109           d​qtz_trigger table  i need triggers with state "paused"​job_name            trigger_state b                     pauseda                     pausede                     normalc                     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;

 
select count(t.job_name) as jobs, v.value as categoryfrom list_values vleft join params p    on  p.list_value_id = v.list_value_id    and p.attr_id       = v.attr_type_def_idleft join qrtz_triggers t    on  t.job_name      = p.object_id    and t.trigger_state = 'PAUSED'where v.attr_type_def_id = 9158075154713931109group 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.

Advertisement

Answer