Solved

Problem selecting rows that have only one assigned status

Posted on 2007-11-14
2
175 Views
Last Modified: 2010-03-20
Hi,

I am trying to modify an SQL statement to retrieve teachers that have only one course listed and whose status is cancelled and type is fulltime.
(i.e. those fulltime teachers that are listed against ONE course that is cancelled)

course_id   course_name        teacher_id      teacher_type      status
-------------------------------------------------------------------------------------
001            French               Barry           fulltime               cancelled
002            English                Mary            fulltime                available
003            German              Mary                fulltime             cancelled
004             History                Colm             fulltime                postponed
005            Art                    Barry               parttime           cancelled

This SQL works for the most part, but will return a teacher_id that is listed against 2 cancelled courses (not allowed!)

select distinct teacher_id
from temp
where status='cancelled' and teacher_type='fulltime'
minus
select teacher_id
from temp
where status in ('available', 'postponed')

Any suggestions ?
0
Comment
Question by:barney75
2 Comments
 
LVL 27

Accepted Solution

by:
MikeToole earned 100 total points
ID: 20278980
Off the top of my head, this should so it:

select teacherID
From temp
Where status='cancelled' and teacher_type='fulltime'
Group By teacherID
Having count(teacherID) = 1
0
 
LVL 18

Expert Comment

by:JR2003
ID: 20279071
select distinct teacher_id
from temp
where status='cancelled' and teacher_type='fulltime'
minus
(
    select teacher_id
    from temp
    where status in ('available', 'postponed')
    union
    select teacher_id
    from temp
    where status = 'cancelled'
    and teacher_type='fulltime'
    group by teacher_id
    having count(*) > 1
)
)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql join/ assign small # first 10 83
how to do this MS SQL script? 11 56
sql query Help 12 53
SQL Server CASE .. WHEN .. IN statement - Syntax issue 4 51
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now