koji deo ovoga sto je Thomas napisao ti nije jasan ? realno, moze da se izbaci t_friend.id iz t_friend tabele i da t_friend.userid i t_friend.friendid budu kompozitni primarni kljuc (sto bi bilo bolje nego kako je sada i malo bi ubrzalo sve operacije nad bazom), ali generalno, to je to ... onaj upit bi morao malo da se prebudzi za mysql ...
Code:
mysql> drop table t_user;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE t_user (id int auto_increment primary key, name varchar(255) NOT NULL);
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> CREATE TABLE t_friend (userid int NOT NULL, friendid int NOT NULL, PRIMARY KEY (userid, friendid));
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> INSERT INTO `t_user` (name) VALUES ('pera'), ('mika'), ('zika'), ('laza'),('selma'),('zozefina'),('enena');
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `t_friend` VALUES (1,2),(1,3),(1,5),(2,3),(2,5),(2,6),(3,7),(3,4);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> -- koga zna pera
mysql> SELECT k1.friendid FROM t_friend k1 WHERE k1.userid=1;
+----------+
| friendid |
+----------+
| 2 |
| 3 |
| 5 |
+----------+
3 rows in set (0.00 sec)
mysql> -- do koga sve pera moze da dodje preko prvog kolena
mysql> SELECT k1.friendid, k2.friendid FROM (SELECT t_friend.friendid FROM t_friend WHERE t_friend.userid=1) k1 JOIN t_friend k2 ON (k2.userid = k1.friendid);
+----------+----------+
| friendid | friendid |
+----------+----------+
| 2 | 3 |
| 2 | 5 |
| 2 | 6 |
| 3 | 4 |
| 3 | 7 |
+----------+----------+
5 rows in set (0.00 sec)
mysql> -- ili ovako, mada je ovo prethodno bolje
mysql> SELECT k1.friendid, k2.friendid FROM (SELECT t_friend.friendid FROM t_friend WHERE t_friend.userid=1) k1 LEFT JOIN t_friend k2 ON (k2.userid = k1.friendid);
+----------+----------+
| friendid | friendid |
+----------+----------+
| 2 | 3 |
| 2 | 5 |
| 2 | 6 |
| 3 | 4 |
| 3 | 7 |
| 5 | NULL |
+----------+----------+
6 rows in set (0.00 sec)
mysql> -- ocemo trece koleno ?
mysql> SELECT k1.friendid, k2.friendid, k3.friendid FROM (SELECT t_friend.friendid FROM t_friend WHERE t_friend.userid=1) k1 JOIN t_friend k2 ON (k2.userid = k1.friendid) JOIN t_friend k3 ON (k3.userid = k2.friendid);
+----------+----------+----------+
| friendid | friendid | friendid |
+----------+----------+----------+
| 2 | 3 | 4 |
| 2 | 3 | 7 |
+----------+----------+----------+
2 rows in set (0.00 sec)
mysql> -- ili ovako?
mysql> SELECT k1.friendid, k2.friendid, k3.friendid FROM (SELECT t_friend.friendid FROM t_friend WHERE t_friend.userid=1) k1 LEFT JOIN t_friend k2 ON (k2.userid = k1.friendid) LEFT JOIN t_friend k3 ON (k3.userid = k2.friendid);
+----------+----------+----------+
| friendid | friendid | friendid |
+----------+----------+----------+
| 2 | 3 | 4 |
| 2 | 3 | 7 |
| 2 | 5 | NULL |
| 2 | 6 | NULL |
| 3 | 4 | NULL |
| 3 | 7 | NULL |
| 5 | NULL | NULL |
+----------+----------+----------+
7 rows in set (0.00 sec)
mislim da dalje umes sam ...