Posted by osa on 04/04/07 23:18

On Apr 4, 12:05 am, Ed Murphy <emurph...@socal.rr.com> wrote:
> 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'))

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

output all of them.

Thanks to all who replied. All the solutions work. 'rec_id' is the
primary key - I should have made that clear.

[Back to original message]