SQL query 2 tables using String without intersect

Question

I am new to SQL and have been using SQLite I am wondering if it is possible to conduct an Intersect or by doing some kind of join instead?

I am trying to search 2 tables to find "What employees worked on any projects John Smith did" but to not show John Smith in the results?

Table 1: (T1)

EmpNum	Lname	Fname
101	Smith	John
102	Haris	Sam
103	xxxxx	Jack

Table 2: (T2)

ProjNum	EmpNum
1013	101
1013	102
1014	102
1014	103
1014	105

how would I re-write this code so that I am searching by a last name instead of EmpNum? editing EmpNum to T1.Lname results in no results.

    SELECT T1.EMPNUM, T1.FNAME, T1.LNAME FROM T1
    INNER JOIN T2
    ON T1.EMPNUM = T2.EMPNUM
    WHERE PROJNUM IN( SELECT PROJNUM
                      FROM T2 WHERE T2.EMPNUM = '101'
                      GROUP BY PROJNUM)
   AND T2.EMPNUM NOT LIKE '101'



       

Answer 1

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

This will return which Employees have worked on a Project with a John Smith¹, 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).

¹ 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.

Answer 2

Option 1

You want employees that are not John Smith:

SELECT
  TgtEmp.EmpNum
, TgtEmp.Fname
, TgtEmp.Lname
FROM
  T1 AS TgtEmp
WHERE
  NOT (TgtEmp.Fname = 'John' AND TgtEmp.Lname = 'Smith')
;

They each must have some projects:

...
WHERE
  NOT (TgtEmp.Fname = 'John' AND TgtEmp.Lname = 'Smith')
  AND EXISTS
  (
    SELECT
      ...
    FROM
      T2 AS TgtProjEmp
    WHERE
      TgtProjEmp.EmpNum = TgtEmp.EmpNum
    ...
  )

And the list of those projects must intersect with the list of projects done by John(s) Smith(s):

...
  AND EXISTS
  (
    SELECT
      TgtProjEmp.ProjNum
    FROM
      T2 AS TgtProjEmp
    WHERE
      TgtProjEmp.EmpNum = TgtEmp.EmpNum

    INTERSECT

    SELECT
      SrcProjEmp.ProjNum
    FROM
      T2 AS SrcProjEmp
    WHERE
      SrcProjEmp.EmpNum IN
      (
        SELECT
          SrcEmp.EmpNum
        FROM
          T1 AS SrcEmp
        WHERE
          (SrcEmp.Fname = 'John' AND SrcEmp.Lname = 'Smith')
      )
  )

Putting everything together, we will get something like this:

SELECT
  TgtEmp.EmpNum
, TgtEmp.Fname
, TgtEmp.Lname
FROM
  T1 AS TgtEmp
WHERE
  NOT (TgtEmp.Fname = 'John' AND TgtEmp.Lname = 'Smith')
  AND EXISTS
  (
    SELECT
      TgtProjEmp.ProjNum
    FROM
      T2 AS TgtProjEmp
    WHERE
      TgtProjEmp.EmpNum = TgtEmp.EmpNum

    INTERSECT

    SELECT
      SrcProjEmp.ProjNum
    FROM
      T2 AS SrcProjEmp
    WHERE
      SrcProjEmp.EmpNum IN
      (
        SELECT
          SrcEmp.EmpNum
        FROM
          T1 AS SrcEmp
        WHERE
          (SrcEmp.Fname = 'John' AND SrcEmp.Lname = 'Smith')
      )
  )
;

---

Option 2

That is not the only option. There is also an approach that does not involve referencing the tables multiple times. First, you join the two tables together and use window aggregation to mark projects that were done by (a) John Smith.

SELECT
  Emp.EmpNum
, Emp.Fname
, Emp.Lname
, MAX(CASE WHEN Emp.Fname = 'John' AND Emp.Lname = 'Smith' THEN 1 END)
  OVER (PARTITION BY ProjEmp.ProjNum) AS HasProjectDoneByJohnSmith
FROM
  T1 AS Emp
  INNER JOIN T2 AS ProjEmp ON Emp.EmpNum = ProjEmp.EmpNum

Then you use the above as either a derived table or a CTE and pull all the distinct employee names that are not John Smith where HasProjectDoneByJohnSmith equals to 1:

• derived table:

  SELECT DISTINCT
    EmpNum
  , Fname
  , Lname
  FROM
    (
      SELECT
        Emp.EmpNum
      , Emp.Fname
      , Emp.Lname
      , MAX(CASE WHEN Emp.Fname = 'John' AND Emp.Lname = 'Smith' THEN 1 END)
        OVER (PARTITION BY ProjEmp.ProjNum)  AS HasProjectDoneByJohnSmith
      FROM
        T1 AS Emp
        INNER JOIN T2 AS ProjEmp ON Emp.EmpNum = ProjEmp.EmpNum
    ) AS derived
  WHERE
    NOT (Fname = 'John' AND Lname = 'Smith')
    AND HasProjectDoneByJohnSmith = 1
  ;
  

• CTE:

  WITH
    EmployeeProjects AS
    (
      SELECT
        Emp.EmpNum
      , Emp.Fname
      , Emp.Lname
      , MAX(CASE WHEN Emp.Fname = 'John' AND Emp.Lname = 'Smith' THEN 1 END)
        OVER (PARTITION BY ProjEmp.ProjNum)  AS HasProjectDoneByJohnSmith
      FROM
        T1 AS Emp
        INNER JOIN T2 AS ProjEmp ON Emp.EmpNum = ProjEmp.EmpNum
    )
  SELECT DISTINCT
    EmpNum
  , Fname
  , Lname
  FROM
    EmployeeProjects
  WHERE
    NOT (Fname = 'John' AND Lname = 'Smith')
    AND HasProjectDoneByJohnSmith = 1
  ;
  

---

All three final queries can be found in this live demo at db<>fiddle.