select * from jobers;
+--------+--------+
| userid | overid |
+--------+--------+
| 2 | 6 |
| 6 | 18 |
| 9 | 2 |
| 16 | 6 |
+--------+--------+
#1 Try this:
select x2.userid as j1,x3.userid as j2,x4.userid as j3 from jobers as x2,jobers as x3,jobers as x4 where x2.overid=18 and x3.overid=x2.userid and x4.overid!=x2.overid and x4.overid!=x2.userid;
. Result:
+------+------+------+
| j1 | j2 | j3 |
+------+------+------+
| 6 | 2 | 9 |
| 6 | 16 | 9 |
+------+------+------+
#2:
select x2.userid as j1,x3.userid as j2 from jobers as x2,jobers as x3 where x2.overid=6 and x3.overid!=x2.overid and x3.overid!=(select jobers.overid from jobers where jobers.userid=x2.overid);
. Result:
+------+------+
| j1 | j2 |
+------+------+
| 2 | 9 |
| 16 | 9 |
+------+------+
Nie oto pewnie chodziło, więc podaję inne rozwiązanie:
select * from em;
+--------+--------+
| userid | overid |
+--------+--------+
| 7 | 18 |
| 6 | 7 |
| 14 | 7 |
| 8 | 7 |
| 3 | 6 |
| 2 | 3 |
| 13 | 8 |
| 10 | 13 |
| 1 | 8 |
| 12 | 1 |
| 14 | 12 |
| 15 | 14 |
| 16 | 14 |
| 17 | 14 |
+--------+--------+
set @x = 3;
SELECT distinct em1.userid as worker,em2.userid as sub_worker,em4.userid as subordinate from em
left join em as em1 on em1.overid = @x
left join em as em2 on (em2.overid in (em1.userid))
left join em as em4 on em4.overid not in (select userid from em where overid = @x) and em4.userid not in (SELECT userid from em where em.userid in (select userid from em where overid = @x));
. Zmieniając zmienną "x" ujrzysz najprawdopodobniej oczekiwane przez Ciebie rezultaty.
Dla: SET @x = 3;, wynik to:
+--------+------------+-------------+
| worker | sub_worker | subordinate |
+--------+------------+-------------+
| 2 | NULL | 7 |
| 2 | NULL | 6 |
| 2 | NULL | 14 |
| 2 | NULL | 8 |
| 2 | NULL | 3 |
| 2 | NULL | 13 |
| 2 | NULL | 10 |
| 2 | NULL | 1 |
| 2 | NULL | 12 |
| 2 | NULL | 15 |
| 2 | NULL | 16 |
| 2 | NULL | 17 |
+--------+------------+-------------+
, a dla SET @x = 1;, taki:
+--------+------------+-------------+
| worker | sub_worker | subordinate |
+--------+------------+-------------+
| 12 | 14 | 7 |
| 12 | 14 | 6 |
| 12 | 14 | 14 |
| 12 | 14 | 8 |
| 12 | 14 | 3 |
| 12 | 14 | 2 |
| 12 | 14 | 13 |
| 12 | 14 | 10 |
| 12 | 14 | 1 |
| 12 | 14 | 15 |
| 12 | 14 | 16 |
| 12 | 14 | 17 |
+--------+------------+-------------+
. Natomiast dla SET @x = 44;:
+--------+------------+-------------+
| worker | sub_worker | subordinate |
+--------+------------+-------------+
| NULL | NULL | 7 |
| NULL | NULL | 6 |
| NULL | NULL | 14 |
| NULL | NULL | 8 |
| NULL | NULL | 3 |
| NULL | NULL | 2 |
| NULL | NULL | 13 |
| NULL | NULL | 10 |
| NULL | NULL | 1 |
| NULL | NULL | 12 |
| NULL | NULL | 15 |
| NULL | NULL | 16 |
| NULL | NULL | 17 |
+--------+------------+-------------+
. Powyższe dwa rozwiązania są błędne z założeń Twojego zadania o ile dobrze zrozumiałem, ostatecznym rozwiązaniem tego problemu jest ten kod:
SELECT distinct em1.userid as worker,em2.userid as sub_worker,em4.userid as subordinate from em
left join em as em1 on em1.overid = 1
left join em as em2 on em2.overid in (em1.userid)
left join em as em4 on em4.overid = (em2.userid);
.