Skip to main content
Database Administrators

Return to Answer

Fixed logic error, removed subquery, additional comments
Source Link
user212533
user212533

This can be done directly, without a CTE or subquery:

This will return which Employees have worked on a Project with a John Smith1, but who are not a John Smith:

SELECT DISTINCT
  Coworker.EmpNum
 ,Coworker.Fname
 ,Coworker.Lname
FROM
  t1 Emp
INNER JOIN
  t2 EmpProject
    ON EmpProject.EmpNum = Emp.EmpNum
INNER JOIN
  t2 CoworkerProject
    ON CoworkerProject.ProjNum = EmpProject.ProjNum
INNER JOIN
  t1 Coworker
    ONAND CoworkerCoworkerProject.EmpNum =<> CoworkerProjectEmp.EmpNum
INNER JOIN
  t1 Coworker
    ANDON Coworker.EmpNum <>= EmpCoworkerProject.EmpNum
WHERE
  Emp.Fname = 'John'
    AND Emp.Lname = 'Smith'

To explain the logic of the query:

  1. Find all EmpNum where FName = 'John'and Lname = 'Smith'.
  2. Find all all ProjNum where EmpNum matches results from 1. Because the primary key should be (ProjNum,EmpNum) this will yield one row per ProjNum.
  3. Find all EmpNum where the ProjNum matches those from 2 but EmpNum is not equal to any EmpNum found in 1.
  4. Retrieve Fname and Lname for all EmpNum in 3.
  5. Remove duplicates (same Employee could work on multiple projects with a John Smith).

1 Obviously there could be multiple John Smiths so it might be better to select a particular John Smith's EmpNum directly before searching, but if you're not concerned with which John Smith the above will suffice.

This can be done directly, without a CTE or subquery:

This will return which Employees have worked on a Project with a John Smith1, but who are not a John Smith:

SELECT DISTINCT
  Coworker.EmpNum
 ,Coworker.Fname
 ,Coworker.Lname
FROM
  t1 Emp
INNER JOIN
  t2 EmpProject
    ON EmpProject.EmpNum = Emp.EmpNum
INNER JOIN
  t2 CoworkerProject
    ON CoworkerProject.ProjNum = EmpProject.ProjNum
INNER JOIN
  t1 Coworker
    ON Coworker.EmpNum = CoworkerProject.EmpNum
        AND Coworker.EmpNum <> Emp.EmpNum
WHERE
  Emp.Fname = 'John'
    AND Emp.Lname = 'Smith'

1 Obviously there could be multiple John Smiths so it might be better to select a particular John Smith's EmpNum directly before searching, but if you're not concerned with which John Smith the above will suffice.

This can be done directly, without a CTE or subquery:

This will return which Employees have worked on a Project with a John Smith1, but who are not a John Smith:

SELECT DISTINCT
  Coworker.EmpNum
 ,Coworker.Fname
 ,Coworker.Lname
FROM
  t1 Emp
INNER JOIN
  t2 EmpProject
    ON EmpProject.EmpNum = Emp.EmpNum
INNER JOIN
  t2 CoworkerProject
    ON CoworkerProject.ProjNum = EmpProject.ProjNum
        AND CoworkerProject.EmpNum <> Emp.EmpNum
INNER JOIN
  t1 Coworker
    ON Coworker.EmpNum = CoworkerProject.EmpNum
WHERE
  Emp.Fname = 'John'
    AND Emp.Lname = 'Smith'

To explain the logic of the query:

  1. Find all EmpNum where FName = 'John'and Lname = 'Smith'.
  2. Find all all ProjNum where EmpNum matches results from 1. Because the primary key should be (ProjNum,EmpNum) this will yield one row per ProjNum.
  3. Find all EmpNum where the ProjNum matches those from 2 but EmpNum is not equal to any EmpNum found in 1.
  4. Retrieve Fname and Lname for all EmpNum in 3.
  5. Remove duplicates (same Employee could work on multiple projects with a John Smith).

1 Obviously there could be multiple John Smiths so it might be better to select a particular John Smith's EmpNum directly before searching, but if you're not concerned with which John Smith the above will suffice.

Fixed logic error, removed subquery
Source Link
user212533
user212533

You need yourThis can be done directly, without a CTE or subquery to reference the Employee table.:

This will return which Employees have worked on a Project with a John Smith1, but who are not a John Smith:

SELECT DISTINCT
  EmpCoworker.EmpNum
 ,EmpCoworker.Fname
 ,EmpCoworker.Lname
FROM
  t2t1 EmpProjEmp
INNER JOIN
  t1t2 EmpEmpProject
    ON EmpEmpProject.EmpNum = EmpProj.EmpNum
WHERE
  EmpProjEmp.EmpNum IN 
   INNER (JOIN
     t2 SELECTCoworkerProject
     ON CoworkerProject.ProjNum = EmpNumEmpProject.ProjNum
     INNER FROMJOIN
        t1
      WHERECoworker
       ON FnameCoworker.EmpNum = 'John'CoworkerProject.EmpNum
          AND LnameCoworker.EmpNum =<> 'Smith'Emp.EmpNum
    )WHERE
    AND NOT (Emp.Fname = 'John'
    AND Emp.Lname = 'Smith')

1 Obviously there could be multiple John Smiths so it might be better to select a particular John Smith's EmpNum directly before searching, but if you're not concerned with which John Smith the above will suffice.

You need your subquery to reference the Employee table.

This will return which Employees have worked on a Project with a John Smith1, but who are not a John Smith:

SELECT DISTINCT
  Emp.EmpNum
 ,Emp.Fname
 ,Emp.Lname
FROM
  t2 EmpProj
INNER JOIN
  t1 Emp
    ON Emp.EmpNum = EmpProj.EmpNum
WHERE
  EmpProj.EmpNum IN 
    (
      SELECT
        EmpNum
      FROM
        t1
      WHERE
        Fname = 'John'
          AND Lname = 'Smith'
    )
    AND NOT (Emp.Fname = 'John' AND Emp.Lname = 'Smith')

1 Obviously there could be multiple John Smiths so it might be better to select a particular John Smith's EmpNum directly before searching, but if you're not concerned with which John Smith the above will suffice.

This can be done directly, without a CTE or subquery:

This will return which Employees have worked on a Project with a John Smith1, but who are not a John Smith:

SELECT DISTINCT
  Coworker.EmpNum
 ,Coworker.Fname
 ,Coworker.Lname
FROM
  t1 Emp
INNER JOIN
  t2 EmpProject
    ON EmpProject.EmpNum = Emp.EmpNum
INNER JOIN
  t2 CoworkerProject
    ON CoworkerProject.ProjNum = EmpProject.ProjNum
INNER JOIN
  t1 Coworker
    ON Coworker.EmpNum = CoworkerProject.EmpNum
        AND Coworker.EmpNum <> Emp.EmpNum
WHERE
  Emp.Fname = 'John'
    AND Emp.Lname = 'Smith'

1 Obviously there could be multiple John Smiths so it might be better to select a particular John Smith's EmpNum directly before searching, but if you're not concerned with which John Smith the above will suffice.

Source Link
user212533
user212533

You need your subquery to reference the Employee table.

This will return which Employees have worked on a Project with a John Smith1, but who are not a John Smith:

SELECT DISTINCT
  Emp.EmpNum
 ,Emp.Fname
 ,Emp.Lname
FROM
  t2 EmpProj
INNER JOIN
  t1 Emp
    ON Emp.EmpNum = EmpProj.EmpNum
WHERE
  EmpProj.EmpNum IN 
    (
      SELECT
        EmpNum
      FROM
        t1
      WHERE
        Fname = 'John'
          AND Lname = 'Smith'
    )
    AND NOT (Emp.Fname = 'John' AND Emp.Lname = 'Smith')

1 Obviously there could be multiple John Smiths so it might be better to select a particular John Smith's EmpNum directly before searching, but if you're not concerned with which John Smith the above will suffice.