Posted by M A Srinivas on 04/04/07 03:21

On Apr 4, 5:00 am, "osa" <osal...@yahoo.com> wrote:
> I have a table 'cust' as follows :
>
> rec_id name dept
> ------- ------- -------
> 1, 'john', 'a'
> 2, 'tom', 'b'
> 3, 'tom', 'a'
> 4, 'tom', 'a'
> 5, 'mary', 'b'
> 6, 'mary', 'a'
> 7, 'mary', 'a'
>
> Looking for SQL to output rows with (dept = b - no other rows with
> that name) OR (dept = a AND no other row for that name with dept = b)
>
> IF dept = b
> THEN
> output only that row for that name
> ELSE IF dept = a AND no other row for that name with dept = b
> THEN
> output that row
>
> Required output :
>
> rec_id name dept
> ------- ------- -------
> 1, 'john', 'a'
> 2, 'tom', 'b'
> 5, 'mary', 'b'
>
> DDL
>
> create table cust (rec_id int, name char(30), dept char(10))
>
> insert into cust values (1, 'john', 'a')
> insert into cust values (2, 'tom', 'b')
> insert into cust values (3, 'tom', 'a')
> insert into cust values (4, 'tom', 'a')
> insert into cust values (5, 'mary', 'b')
> insert into cust values (6, 'mary', 'a')
> insert into cust values (7, 'mary', 'a')
>
> Any ideas for SQL for this ?
>
> Thanks
> Lena

select * from cust a
where ( dept = 'b' OR ( dept= 'a' and not exists( select 1 from cust
b where b.name=a.name and b.dept='b' ))
)

[Back to original message]