SQL Employees Searching for Others in Same Department

Question

I have a business rule that employees cannot purchase items from employees of the same department. I have two tables. one is the list of employees and their IDs:

   Emp_ID Emp_Name  Dept_ID
       1     John        1
       2      Bob        1
       3    Susie        2
       4     Jack        3
       5     Jill        3

And a table of the employee ID and the employee ID they purchased from:

   Emp_ID  Bought_From_Emp_ID
       1                   2
       2                   3
       4                   5
       5                   1

My expected output would be to have the the employee id (or name) of both employees if one purchased an item from the same department:

   Emp_ID  Bought_From_Emp_ID  Same_Dept_ID
       1                   2             1 --John and Bob are in Same Department (1)
       4                   5             3 --Jack and Jill are in Same Department (3)

How would I do this for millions of records? I have a feeling that this is very simple in the long run, but my mind hasn't shifted towards the solution yet.

I am using Teradata, but can use MSSQL if there are any SQL-specific answers

Possible duplicate of [Join table twice - on two different columns of the same table](https://stackoverflow.com/questions/10710271/join-table-twice-on-two-different-columns-of-the-same-table) — Tab Alleman, May 01 '18 at 13:16

score 2 · Answer 1 · answered May 01 '18 at 13:20

DECLARE @emp TABLE
(
    emp_id INT,
    emp_name VARCHAR(20),
    dept_id INT
);
INSERT INTO @emp
(
    emp_id,
    emp_name,
    dept_id
)
VALUES
--Emp_ID Emp_Name  Dept_ID    
(1, 'John ', 1),
(2, 'Bob', 1),
(3, 'Susie', 2),
(4, 'Jack', 3),
(5, 'Jill', 3);

DECLARE @purch TABLE
(
    emp_id INT,
    Bought_From_Emp_ID INT
);
INSERT INTO @purch
(
    emp_id,
    Bought_From_Emp_ID
)
VALUES
(1, 2),
(2, 3),
(4, 5),
(5, 1);


SELECT e.emp_id,
       e1.emp_id AS Bought_From_Emp_ID,
       e.dept_id AS Same_Dept_ID
FROM @purch p
    JOIN @emp e
        ON p.emp_id = e.emp_id
    JOIN @emp e1
        ON p.Bought_From_Emp_ID = e1.emp_id
           AND e.dept_id = e1.dept_id
WHERE e1.emp_id <> e.emp_id;

I like the simplicity of this! let me try and reverse engineer this for my own understanding. — MattR, May 01 '18 at 13:43

score 1 · Accepted Answer · answered May 01 '18 at 13:52

Try this query

SELECT purch.emp_id             AS EmpID, 
       purch.bought_from_emp_id AS BoughtFrom, 
       T1.dept_id               AS department 
FROM   purch 
       INNER JOIN emp T1 
               ON T1.emp_id = purch.emp_id 
       INNER JOIN emp T2 
               ON T2.emp_id = purch.bought_from_emp_id 
WHERE  t1.dept_id = t2.dept_id

Output

+-------+------------+------------+
| EmpID | BoughtFrom | department |
+-------+------------+------------+
|     1 |          2 |          1 |
|     4 |          5 |          3 |
+-------+------------+------------+

Demo: http://www.sqlfiddle.com/#!18/22746/1/0

SQL Employees Searching for Others in Same Department

2 Answers2