Posted by Ed Murphy on 04/04/07 04:05

M A Srinivas wrote:

> On Apr 4, 5:00 am, "osa" <osal...@yahoo.com> wrote:

>> 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

If a name has multiple rows with dept = 'b', do you want to output
all of them? If not, then which one do you want to output?

This question does not affect dept = 'a', since you don't have "only"
attached to it.

>> create table cust (rec_id int, name char(30), dept char(10))

rec_id should be a primary key.

>> 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')

This indicates that (name, dept) is not a unique key. It would be
possible to append this:

insert into cust values (8, 'mary', 'b')

> 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' ))
> )

Another way to do it:

select *
from cust
where dept = 'b'
or (dept = 'a' and name not in
(select name from cust where dept = 'b'))

[Back to original message]