|
|
Posted by osa on 04/04/07 00:00
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
Navigation:
[Reply to this message]
|