SQL query did not return any values

Question

Hi i have the following table structure.

Professor (EMP ID,Name,Status,Salary,Age)
Course(Course ID,Course Name,Points)
Works(Course ID,EMP ID,Class ID)

I need to do the following.

Return List of Employees who have taken 2 different course M1 and M2 for the same class ‘class 10’

This is the query that i have written.

SELECT p.EmpID, p.Name, p.Status, p.Salary 
FROM professor p, course c, works w 
WHERE p.EmpID = w.EmpID
AND
w.CourseID = c.CourseID
AND
w.ClassID = 10
AND
c.CourseName IN ( SELECT CourseName FROM course WHERE CourseName = 'm1'
AND CourseName = 'm2')

But the query doesnot return any values even though there are data in the db.

is coursename nullable?

Mitch Wheat
– Mitch Wheat

2013-03-08 06:09:37 +00:00
Commented Mar 8, 2013 at 6:09 — Mitch Wheat
– Mitch Wheat, Commented Mar 8, 2013 at 6:09

John Woo · Accepted Answer · 2013-03-08 06:26:58Z

2

This problem is commonly called Relational Division

SELECT  a.EmpID, a.name
FROM    Professor a
        INNER JOIN Works b
            ON a.EmpID = b.EmpID AND b.ClassID = 10 
        INNER JOIN  Course c
            ON b.CourseID = c.CourseID
WHERE   c.CourseNAME IN ('M1', 'M2')
GROUP   BY a.EmpID, a.name
HAVING  COUNT(DISTINCT c.CourseNAME) = 2

SQL of Relational Division

edited Mar 8, 2013 at 6:26

answered Mar 8, 2013 at 6:12

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1844638 Over a year ago

i am getting this error when i execute your query - Column 'EmpID' in on clause is ambiguous

John Woo Over a year ago

oops my bad sorry for the typo, try again. a.EmpID = EmpID should be a.EmpID = b.EmpID

Udo Klein · Accepted Answer · 2013-03-08 06:09:36Z

1

The subquery

( SELECT CourseName FROM course WHERE CourseName = 'm1' AND CourseName = 'm2')

will return nothing. Look at the "AND"

answered Mar 8, 2013 at 6:09

Udo Klein

6,9401 gold badge39 silver badges63 bronze badges

2 Comments

John Woo Over a year ago

CourseName = 'm1' AND CourseName = 'm2' will ALWAYS return FALSE

Udo Klein Over a year ago

Exactly this is my point. Hence the IN ... will never find anything and hence the whole query will return nothing

Collectives™ on Stack Overflow

SQL query did not return any values

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related