Re: how to write query for this purpose

MadhavC schrieb:
> 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.
>

Select WoNo
from worktran w1
where not exist
(

    select *
    from worktran w2
    where w1.WoNo = w2.WoNo
    and w2.Status != 'Fullfill'
)

Another solution:

select WoNo
from worktran
group by WoNo
having min(status)='Fullfill' and max(status)='Fullfill'

Hope this helps

Martin

-- 
Firma/Company:                                              CRESD GmbH
Phone: +49-89-65 30 95 63                      Fax: +49-89-65 30 95 64
WWW:                                               http://www.cresd.de
S-Mail:                                Freibadstr. 14, D-81543 München
PGP-Key:                            http://www.cresd.de/edv/pgpkey.txt
Open BC (Einladung)            http://www.openbc.com/go/invita/4561755