mysql query not returning expected values

Question

Hi i am writing a mysql query for the following db structure.

Professor (EMP ID,Name,Status,Salary,Age)
Course(Course ID,Course Name,Points)
Works(Course ID,EMP ID,Class ID)
Assumptions:
Each course has only one instructor in each semester
Status can take values from “Full”, “Associate”, and “Assistant”

I need to do the following.

Return the names of full professors who ever taught at least two courses IN one Class

SELECT p.name
FROM professor p, works w
WHERE p.empid = w.empid
AND p.status = ‘full’
AND w.classid IN (SELECT classid
FROM works
WHERE count(courseid)>1)

Return the name(s) of the professor(s) who taught the most number of courses IN Class 10

SELECT p.names
FROM professor p, works w
WHERE p.empid =w.empid
AND w.classid IN (SELECT classid
FROM works
WHERE classid = 10
AND coursed = max(coursed))

But these queries are returning wrong results. I am new to mysql please help.

You should read about explicit joins, GROUP BY and aggregates. — Jakub Kania
– Jakub Kania, Commented Mar 8, 2013 at 11:48

Muhammad Raheel · Accepted Answer · 2013-03-08 11:41:44Z

1

Try it like this

SELECT 
    p.*,
    COUNT(c.Course_ID) AS TCount
FROM Professor AS P
LEFT JOIN Works AS w ON w.EMP_ID = p.EMP_ID
LEFT JOIN Course AS c ON w.Course_ID = c.Course_ID
GROUP BY p.EMP_ID , c.Course_ID
HAVING TCount > 1

answered Mar 8, 2013 at 11:41

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

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

2 Comments

user1844638 Over a year ago

This is for which question i have 2

Jakub Kania Over a year ago

@user1844638 Can't you at very least test it and see for yourself?

Jakub Kania · Accepted Answer · 2013-03-08 12:49:02Z

1

Alternative answer for the first one:

SELECT DISTINCT p.empid, p.name
FROM professor AS p 
JOIN works AS w ON
    p.empid = w.empid
JOIN works w2 ON
    w.empid=w2.empid AND w.classid=w2.classid AND w.courseid<>w2.courseid

edited Mar 8, 2013 at 12:49

answered Mar 8, 2013 at 11:46

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

Collectives™ on Stack Overflow

mysql query not returning expected values

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related