Ron, I'm not sure exactly what you are looking for, but based on the four queries you provided, and I fixed the last one based on my assumptions, you get...

select (count(ip.process1status)) from #item_process ip where item_id = 12 and process1status <> 0

select (count(ip.process2status)) from #item_process ip where item_id = 12 and process2status <> 0

select (count(it.transstatus)) from #item_trans it where item_id = 12 and transstatus <> 0

select i.item_name from #item i, #item_process p where i.item_id = 12 and i.item_id = p.item_id AND p.item_page like '10_001%'

Output

1

1

2

MyBook

I think this is maybe what you are looking for?

SELECT

i.item_name,

t1.item_id,

t1.item_page,

t1.process1status,

t1.process2status

FROM #item i,

(--Derived table t1 counts the item_id in each process

SELECT

item_id,

item_page,

process1status = SUM(CASE WHEN process1status <> 0 THEN 1 ELSE 0 END),

process2status = SUM(CASE WHEN process2status <> 0 THEN 1 ELSE 0 END)

FROM #item_process

GROUP BY item_id,item_page

) t1--Should be an end paren before the 't1'. I can't get it to show.

WHERE i.item_id = t1.item_id

--Output

--My_book1210_00101

--My_book1210_00210

Let us know if this is at least headed in the right direction