This is what I tried and succeeded.
select
(
select (count(ip.process1status)) from item_process ip where ip.item_id = i.item_id and process1status <> 0 and p.pagename like '10_001%' ,
select (count(ip.process2status)) from item_process ip where ip.item_id = i.item_id and process2status <> 0 and p.pagename like '10_001%',
select (count(it.transstatus)) from item_trans it where it.item_id = i.item_id and transstatus <> 0),
select i.itemname
)
from item i where i.item_id = 12
Thanks for the helping hand you extended a lot Greg,
Ron.