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

On Apr 4, 5:00 am, "osa" <osal... (AT) yahoo (DOT) com> wrote:
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' ))
)

M A Srinivas wrote:

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.

rec_id should be a primary key.

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

insert into cust values (8, 'mary', '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'))

Please stop using silly, non-relational record numbering!! I am going
to assume that the impropery named columns are the key and that you
meant to post:

CREATE TABLE DeptAssignments
(emp_name CHAR(15) NOT NULL
REFERENCES Personnel (emp_name),
dept_name CHAR(1) NOT NULL
REFERENCES Departments(dept_name),
PRIMARY KEY (emp_name, dept_name));

SELECT emp_name
FROM DeptAssignments
GROUP BY emp_name
HAVING MIN(dept_name) = 'b' -- HAS ONE OR MORE B'S
OR (MIN(dept_name = 'a') AND MAX(dept_name = 'a'); -- A'S ONLY

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

Yes, which is why the **minimal** Netiquette is to post real DDL. I
hope to Ghod you are not just numbering physical rows in your table
with IDENTITY or other non-relational crap. Can you share with us
what it means in your data model? How is it validated and verified?

If you have merely mimicked a sequential file in SQL, your data
integrity is gone.

On Apr 5, 2:56 am, "--CELKO--" <jcelko... (AT) earthlink (DOT) net> wrote:
Your query does not provide the output (rec_id,name,dept) requested.
It is just listing name (emp_name)