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...@yahoo.com> wrote:
> 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

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:

> 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'))

>> I have a table 'cust' as follows : <<

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));

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

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...@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.

>> '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...@earthlink.net> wrote:
> >> I have a table 'cust' as follows : <<
>
> 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));
>
> >> 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) <<
>
> 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

Your query does not provide the output (rec_id,name,dept) requested.
It is just listing name (emp_name)