enter image description here

Query 4. Make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project.

I used join on instead of where ..
Is this query correct?

my query

select DISTINCT pnumber
FROM PROJECT
WHERE
    Pnumber IN
    (
        SELECT Pnumber
        FROM PROJECT AS pro
        JOIN DEPARTMENT AS dept ON  pro.Dnum = dept.Dnumber
        JOIN EMPLOYEE AS emp ON dept.Mgr_ssn = emp.Ssn
        WHERE Lname = "Smith"
    )
    OR 
    Pnumber IN
    (
        SELECT Pno
        FROM PROJECT AS pro
        JOIN WORKS_ON AS wo ON pro.Pnumber = wo.Pno
        JOIN EMPLOYEE AS emp ON wo.Essn = emp.Ssn
        WHERE Lname = "Smith"
    );

origin query

SELECT DISTINCT Pnumber
FROM PROJECT
WHERE 
    Pnumber IN
    ( 
         SELECT Pnumber
         FROM PROJECT, DEPARTMENT, EMPLOYEE
         WHERE Dnum = Dnumber AND Mgr_ssn = Ssn  
         AND Lname = "Smith"
     )
     OR
     Pnumber IN
     (
         SELECT Pno
         FROM WORKS_ON, EMPLOYEE
         WHERE Essn = Ssn 
         AND Lname = "Smith" 
     );

CodePudding user response：

The query in the last set of parentheses should be:

SELECT Pno
FROM WORKS_ON
JOIN EMPLOYEE ON Essn = Ssn
WHERE Lname = 'Smith'

In the query you wrote, you are also joining the PROJECT table which is not being done in the original query provided.