Robert
Reputation: 10390
SQL Self Join with no repetition
Employee table:
+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
| emp_id | fname | lname | start_date | end_date | superior_emp_id | dept_id | title | assigned_branch_id |
+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
| 1 | Michael | Smith | 2005-06-22 | NULL | NULL | 3 | President | 1 |
| 2 | Susan | Barker | 2006-09-12 | NULL | 1 | 3 | Vice President | 1 |
| 3 | Robert | Tyler | 2005-02-09 | NULL | 1 | 3 | Treasurer | 1 |
| 4 | Susan | Hawthorne | 2006-04-24 | NULL | 3 | 1 | Operations Manager | 1 |
| 5 | John | Gooding | 2007-11-14 | NULL | 4 | 2 | Loan Manager | 1 |
| 6 | Helen | Fleming | 2008-03-17 | NULL | 4 | 1 | Head Teller | 1 |
| 7 | Chris | Tucker | 2008-09-15 | NULL | 6 | 1 | Teller | 1 |
| 8 | Sarah | Parker | 2006-12-02 | NULL | 6 | 1 | Teller | 1 |
| 9 | Jane | Grossman | 2006-05-03 | NULL | 6 | 1 | Teller | 1 |
| 10 | Paula | Roberts | 2006-07-27 | NULL | 4 | 1 | Head Teller | 2 |
| 11 | Thomas | Ziegler | 2004-10-23 | NULL | 10 | 1 | Teller | 2 |
| 12 | Samantha | Jameson | 2007-01-08 | NULL | 10 | 1 | Teller | 2 |
| 13 | John | Blake | 2004-05-11 | NULL | 4 | 1 | Head Teller | 3 |
| 14 | Cindy | Mason | 2006-08-09 | NULL | 13 | 1 | Teller | 3 |
| 15 | Frank | Portman | 2007-04-01 | NULL | 13 | 1 | Teller | 3 |
| 16 | Theresa | Markham | 2005-03-15 | NULL | 4 | 1 | Head Teller | 4 |
| 17 | Beth | Fowler | 2006-06-29 | NULL | 16 | 1 | Teller | 4 |
| 18 | Rick | Tulman | 2006-12-12 | NULL | 16 | 1 | Teller | 4 |
+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
Query:
SELECT e1.fname, e1.lname,
'VS' AS vs,
e2.fname, e2.lname
FROM employee e1
INNER JOIN employee e2
ON e1.emp_id < e2.emp_id
WHERE e1.title = "Teller" AND e2.title = "Teller";
Result:
+----------+----------+----+----------+----------+
| fname | lname | vs | fname | lname |
+----------+----------+----+----------+----------+
| Chris | Tucker | VS | Sarah | Parker |
| Chris | Tucker | VS | Jane | Grossman |
| Chris | Tucker | VS | Thomas | Ziegler |
| Chris | Tucker | VS | Samantha | Jameson |
| Chris | Tucker | VS | Cindy | Mason |
| Chris | Tucker | VS | Frank | Portman |
| Chris | Tucker | VS | Beth | Fowler |
| Chris | Tucker | VS | Rick | Tulman |
| Sarah | Parker | VS | Jane | Grossman |
| Sarah | Parker | VS | Thomas | Ziegler |
| Sarah | Parker | VS | Samantha | Jameson |
| Sarah | Parker | VS | Cindy | Mason |
| Sarah | Parker | VS | Frank | Portman |
| Sarah | Parker | VS | Beth | Fowler |
| Sarah | Parker | VS | Rick | Tulman |
| Jane | Grossman | VS | Thomas | Ziegler |
| Jane | Grossman | VS | Samantha | Jameson |
| Jane | Grossman | VS | Cindy | Mason |
| Jane | Grossman | VS | Frank | Portman |
| Jane | Grossman | VS | Beth | Fowler |
| Jane | Grossman | VS | Rick | Tulman |
| Thomas | Ziegler | VS | Samantha | Jameson |
| Thomas | Ziegler | VS | Cindy | Mason |
| Thomas | Ziegler | VS | Frank | Portman |
| Thomas | Ziegler | VS | Beth | Fowler |
| Thomas | Ziegler | VS | Rick | Tulman |
| Samantha | Jameson | VS | Cindy | Mason |
| Samantha | Jameson | VS | Frank | Portman |
| Samantha | Jameson | VS | Beth | Fowler |
| Samantha | Jameson | VS | Rick | Tulman |
| Cindy | Mason | VS | Frank | Portman |
| Cindy | Mason | VS | Beth | Fowler |
| Cindy | Mason | VS | Rick | Tulman |
| Frank | Portman | VS | Beth | Fowler |
| Frank | Portman | VS | Rick | Tulman |
| Beth | Fowler | VS | Rick | Tulman |
+----------+----------+----+----------+----------+
Intention:
I wanted to pair up people together as if employees were playing a chess game, where one employee will play against only one person in the first round not 3 or 4 different people. For example I don't want Chris Tucker playing against Sarah, Jane, Thomas... etc in the first round. I want him paired up with only one opponent. If he or his adversary looses they are out.
How can I do this?
Upvotes: 1
Views: 85
Answers (1)
Gordon Linoff
Reputation: 1270391
I would approach this by randomly enumerating the names and then choosing them in pairs:
select max(case when seqnum % 2 = 0 then fname end) as fname_1,
max(case when seqnum % 2 = 0 then lname end) as lname_1,
max(case when seqnum % 2 = 1 then fname end) as fname_2,
max(case when seqnum % 2 = 1 then lname end) as lname_2
from (select e.*, (@rn := @rn + 1) as seqnum
from employee e cross join
(select @rn := 0) params
order by rand()
) e
group by floor((seqnum - 1) / 2);
This assumes that you have an even number of employees, which seems to be implicitly assumed as part of the problem.
Upvotes: 1