|
|
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'))
Navigation:
[Reply to this message]
|