CODEWITHSUNDEEP
mysqlsql
JohnDew
JohnDew

Reputation: 13

mySQL - Get Tuples of entries with Same foreign attribute

I've got the following tables:

worker(id, fname, lname birthday)

works_on(worker_id, task_id).

Now, I want to get tuples of all workers working on same task. My idea was to join worker with works_on and then joining with worker again. Basically this is working but I got duplicates like:

John Smith, Walther White

Walther White, John Smith

Hope you can help me.

John

Upvotes: 1

Views: 33

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72195

You can use the following query:

SELECT t1.fname, t1.lname, t4.fname, t4.lname, t2.task_id
FROM worker AS t1
JOIN works_on AS t2 ON t1.id = t2.worker_id
JOIN works_on AS t3 ON t2.task_id = t3.task_id AND t2.worker_id < t3.worker_id
JOIN worker AS t4 ON t3.worker_id = t4.id

The trick here is this predicate in the ON clause of the second JOIN operation:

t2.worker_id < t3.worker_id

This gets the id of a worker working in the same project provided this id value is bigger than the id value of the t1 worker table. This way, duplicate pairs are ruled out.

Demo here

Upvotes: 2

Related Questions