Select command (Solved)

Transact-SQL
1

Hi,

How to select instructors who qualified to teach 'Course 1 and not qualified to teach Course 2 at the same time?

2

Write SELECT query with two pieces in the WHERE clause, one to filter for qualified to teach course 1 = true and the second one to filter for qualified to teach course 2 = false

The exact syntax of how you would write it depends on the data structure. That is to say, where the information about the teacher is stored, and where the information about courses, and qualifications are stored.

1 Like
3

Thanks for your reply

this is the table

+-----------+----------+
| facultyID | CourseID |
+-----------+----------+
|      2143 |     3112 |
|      2143 |     3113 |
|      3467 |     3113 |
|      3467 |     4212 |
|      3467 |     4930 |
|      4756 |     3112 |
|      4756 |     3113 |
+-----------+----------+

I used this command

SELECT FacultyID FROM qualified
WHERE CourseID = 'ISM 3113'
AND NOT (CourseID = 'ISM 4930')

but I get 2143, 3467 and 4756 which I should get only 2143 and 4756

4

Look up EXISTS construct and NOT EXISTS construct in SQL Server. For example here

In your case, the query would be something like this:

SELECT facultyID
FROM qualified AS qa
WHERE 
EXISTS
(
	SELECT * FROM qualified qb 
	WHERE qb.courseID = 'ISM 3113' 
		AND qb.facultyId = qa.facultyId
)
AND NOT EXISTS
(
-- a similar clause as above to include only
-- those facultyIds that don't have an entry 
-- for ISM 4930.
)
1 Like
6

Thank you for your help, it works with me now.

7

For returning only facultyID, the method below is more efficient and, I believe, more flexible. I don't have data to test the code, but I think it's correct.

SELECT facultyID 
FROM qualified
GROUP BY facultyID
HAVING MAX(CASE WHEN courseID = 'ISM 3113' THEN 1 ELSE 0 END) = 1
   AND MAX(CASE WHEN courseID = 'ISM 4930' THEN 1 ELSE 0 END) = 0