CODEWITHSUNDEEP
mysqlsqldatabaserelational-division
user1881440
user1881440

Reputation:

Sql query wrong

Hi i have the following database schema

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

I have written the following query to Return list of employees who have taught course M1 or M2 but NOT both

SELECT p.empid,p.name
FROM professor p,course c,works w
WHERE p.empid = w.empid
AND w.courseid = c.courseid
AND ((c.coursename = ‘m1’ AND c.coursename != ‘m2’)
OR
(c.coursename = ‘m2’ AND c.coursename != ‘m1’))

Is the above query right as i am new to sql please help

Upvotes: 0

Views: 65

Answers (2)

hd1
hd1

Reputation: 34667

Try:

SELECT p.empid,p.name
FROM professor p join works w on
p.empid = w.empid
having count(w.courseid) = 2

This assumes you have 2 classes total, which, while unlikely, it will point you in the right direction.

Upvotes: 0

John Woo
John Woo

Reputation: 263733

This problem is usually termed as Relational Division. What the query below does is it gets all the professor's name which teaches the courses such as m1 and m2. After the professors has been filtered it then counts the number of distinct CourseName. Obviously, it will return one if it only teaches one course :D.

SELECT  a.EmpID, a.Name
FROM    Professor a
        INNER JOIN Works b
            ON a.EmpID = b.EmpID
        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) = 1

Upvotes: 1

Related Questions