問題描述
SQL 查詢沒有返回任何值 (SQL query did not return any values)
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.
參考解法
方法 1:
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
方法 2:
The subquery
( SELECT CourseName FROM course WHERE CourseName = 'm1' AND CourseName = 'm2')
will return nothing. Look at the "AND"
(by user1844638、John Woo、Udo Klein)