TacoB0t TacoB0t - 2 months ago 8
MySQL Question

SQL - Get pairs for Employees from the same city and first employee in pair takes a course taught by second employee

I've been able to narrow this down to the appropriate pairs but I'm at a complete loss on how to filter the final part. The following narrows it down a bit but still contains a pair that are enrolled in a class together (one of the employees is a teacher but happens to be enrolled in the class with the ohter employee thats being returned):

select a.ID, b.ID
from EM as a, EM as b, ER, CR
where a.City = b.City and a.ID <> b.ID and a.ID = ER.ID and b.ID =
CR.Teacher_ID and a.ID < b.ID
group by a.ID, b.id;


The tables I have are:

EM - Employee info with fields ID, City, Name, Dept, salary

ER - Employee enrollment with fields Cnum, ID, Grade

CR - Course info with fields Cnum, Title, DEPT, Teacher_ID (matches EM.ID)

Bold fields are primary keys

The results should look like:

ID | ID
---------
E1 E2
E10 E2


Where both employees are from the same city and more over, the first employee in the pair takes a course from the second employee in the pair.
Except I end up with

ID | ID
---------
E1 E2
E10 E2
E14 E2


I'm getting an employee pair (E14 and E2) that are from the same city but the second employee is not teaching the first employee, they just happen to be in the same class together as students.

**EM:**

+-----+-----------+-------------+------------+--------+
| ID | Name | City | DEPT | Salary |
+-----+-----------+-------------+------------+--------+
| E1 | John | Baton Rouge | Accounting | 78900 |
| E10 | Jasmin | Baton Rouge | Production | 79790 |
| E11 | Joseph | Atlanta | Production | 69250 |
| E12 | Matthew | New Orleans | Research | 82590 |
| E13 | Jonathan | Lafayette | Research | 85270 |
| E14 | James | Baton Rouge | Research | 69190 |
| E2 | Johnson | Baton Rouge | Accounting | 72850 |
| E3 | Mary | New Orleans | Marketing | 67890 |
| E4 | Elizabeth | Lafayette | Marketing | 76840 |
| E5 | David | Baton Rouge | Marketing | 56500 |
| E6 | Eric | New Orleans | Marketing | 86500 |
| E7 | Richard | Houston | Marketing | 76500 |
| E8 | Robert | Memphis | Marketing | 56760 |
| E9 | Rose | Baton Rouge | Production | 66790 |
+-----+-----------+-------------+------------+--------+


**ER:**

+------+-----+-------+
| Cnum | ID | Grade |
+------+-----+-------+
| C1 | E1 | 89 |
| C1 | E2 | 87 |
| C1 | E4 | 93 |
| C2 | E3 | 82 |
| C2 | E6 | 78 |
| C2 | E7 | 80 |
| C3 | E10 | 95 |
| C3 | E14 | 91 |
| C3 | E2 | 94 |
| C3 | E4 | 88 |
| C3 | E9 | 91 |
| C4 | E1 | 71 |
| C4 | E10 | 85 |
| C4 | E11 | 95 |
| C4 | E7 | 84 |
| C4 | E8 | 77 |
| C5 | E1 | 85 |
| C5 | E10 | 97 |
| C5 | E3 | 79 |
+------+-----+-------+

**CR:**

+------+-----------------+------------+------------+
| Cnum | Title | DEPT | Teacher_id |
+------+-----------------+------------+------------+
| C1 | Database | Research | E12 |
| C2 | Market Analysis | Marketing | E4 |
| C3 | Big Data | Research | E13 |
| C4 | Audit | Accounting | E2 |
| C5 | Cloud Computing | Research | E12 |
+------+-----------------+------------+------------+

Answer

Edit Simplified and dumped the derived table.

SELECT DISTINCT
    em.ID as Employee
    ,cr.Teacher_Id as Teacher
FROM
    EM em
    INNER JOIN ER er
    ON em.ID = er.ID
    INNER JOIN CR cr
    ON er.Cnum = cr.Cnum
    INNER JOIN EM te
    ON cr.Teacher_Id = te.ID
    AND em.City = te.City

This will get you all pairs of employees and teachers of some course that live in the same city. DISTINCT is here in case they actually are in 2 courses together.

Note E3 and E12 are also a match as well as E4 and E13 but they are not listed in your example result.