>I have two queries that get the number of defects for a set of tasks/subtasks
>
>This one selects the completed tasks for 2000 and 2001
>
>

>select task.task_num, subtask.stsubtask ;
>  from task, subtask ;
>  where task.task_num = subtask.sttask and ;
>        task.tstatus = "C" and ;
>        task.task_num > "20000000" ;
>  order by task_num, stsubtask ;
>   into cursor temp1	
>

>
>This one counts the defects
>
>

>select temp1.task_num, temp1.stsubtask, count(defect.defect_num) as defect_cnt ;
>  from temp1 left outer join defect ;
>    on temp1.task_num = defect.task and ;
>       temp1.stsubtask = defect.subtask ;
> order by task_num, stsubtask ;
> group by task_num, stsubtask ;
>  into cursor temp2	
>

>
>Is there a way to combine them into one query?
>
>Thanks.

select task.task_num, subtask.stsubtask, ;
	sum(iif(isnull(defect.defect_num),0,1)) as defect_cnt ;
	from task inner join subtask on subtask.sttask = task.task_num ;
		left join defect on defect.task = task.task_num ;
			and defect.subtask = subtask.stsubtask ;
	where task.tstatus = "C" ;
		and task.task_num > "20000000" ;
	order by task.task_num, subtask.stsubtask ;
	group by task.task_num, subtask.stsubtask ;
	into cursor temp2

- Keith