query for this purpose

We have a worktran table
WoNo Jobno Status
1 01 Fullfill
1 02 Pending
2 01 Fullfill
2 02 Fullfill
We need to display those WoNo having all its rows status=fulfill
In the example above, it should display only WoNo 2 because it contain
all Fullfill status
We tried - select distinct WoNo from worktran where status='Fullfill'
But it does not make sure that all of the rows for certian WoNo have
status='Fulfill'
Thanks in advance.Hello, MadhavC
You can use one of these queries:
SELECT WoNo FROM worktran
GROUP BY WoNo
HAVING COUNT(*)=SUM(CASE WHEN Status='Fulfill' THEN 1 ELSE 0 END)
SELECT DISTINCT WoNo FROM worktran a
WHERE NOT EXISTS (
SELECT * FROM worktran b
WHERE a.WoNo=b.WoNo
AND b.WoNo<>'Fulfill'
)
You should also take a look at the following article, by Joe Celko,
regarding Relational Division:
http://www.dbazine.com/ofinterest/oi-articles/celko1
Razvan|||Thanks for your reply it worked.