Reply to SQL question

Your name:

Reply:


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

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация