SQL Query returning the wrong result-CodePudding

For a college assignment, we have to write a bunch of SQL queries based on question we got, the last question states:

`Write a query that will indicate which employee has been booked off for the most number of days by doctor ‘D0001’. Display the employee name and surname, doctor name, and the number of days

My issue is even when I say what value I want to select it return the wrong name of the doctor which I can't understand as to why that happens. The other values in the query are being displayed as it should, the only issue is the Doctor's name from their ID that is incorrectly displayed

The Table

CREATE TABLE DOCTORS(
    DOCTOR_ID VARCHAR(5) NOT NULL PRIMARY KEY,
    DOCTOR_NAME VARCHAR(30) NOT NULL
)

Values We had to add

INSERT INTO DOCTORS (DOCTOR_ID, DOCTOR_NAME) VALUES ('D0001', 'Thabo Ntlali')
INSERT INTO DOCTORS (DOCTOR_ID, DOCTOR_NAME) VALUES ('D0002', 'Deon Coetzee')
INSERT INTO DOCTORS (DOCTOR_ID, DOCTOR_NAME) VALUES ('D0003', 'Kwezi Mbete')
INSERT INTO DOCTORS (DOCTOR_ID, DOCTOR_NAME) VALUES ('D0004', 'Trevor January')
INSERT INTO DOCTORS (DOCTOR_ID, DOCTOR_NAME) VALUES ('D0005', 'Julia Robin')

The query I wrote

SELECT TOP(1) emp.EMPLOYEE_NAME, emp.EMPLOYEE_SURNAME, d.DOCTOR_NAME, MAX(e.NUMBER_OF_DAYS) AS "NUMBER_OF_DAYS"
FROM DOCTORS AS D, EMPLOYEES emp 
JOIN EMPLOYEE_SICKLEAVE e
ON emp.EMPLOYEE_ID = e.EMPLOYEE_ID
WHERE e.DOCTOR_ID = 'D0001' 
GROUP BY emp.EMPLOYEE_NAME, emp.EMPLOYEE_SURNAME, d.DOCTOR_NAME
ORDER BY emp.EMPLOYEE_NAME, EMP.EMPLOYEE_SURNAME

CodePudding user response：

Really important point in the comments

Tip of today: Switch to modern, explicit JOIN syntax everywhere. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.

~ jarlh

So let's take your joins:

...
FROM DOCTORS AS D, EMPLOYEES emp 
JOIN EMPLOYEE_SICKLEAVE e
ON emp.EMPLOYEE_ID = e.EMPLOYEE_ID
...

This is hiding the fact that there are 3 tables, but only one ON clause!

This is likely the cause of your problem.

So let's re-write them [as much as we can]:

...
FROM   DOCTORS
 INNER
  JOIN EMPLOYEES
    ON EMPLOYEES.<SOMETHING> = DOCTORS.<SOMETHING>
 INNER
  JOIN EMPLOYEE_SICKLEAVE
    ON EMPLOYEE_SICKLEAVE.EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID
...

CodePudding user response：

There is another problem in addition to the one corrected by the user gvee. To obtain the employee with the most number of days, you have to order by the sum of days for each employee: ORDER BY SUM(e.NUMBER_OF_DAYS) DESC
Also in the SELECT you have to change MAX() in SUM().