nauci nenade kako se radi join :)
http://dev.mysql.com/doc/refman/5.7/en/join.html
i samo ce ti se reci :D
oces sve poruke abitno dal imaju fajlove ili ne (sa njihovim fajlovima
naravno)
Code:
mysql> create table t1 (t1_id int, val char(10));
Query OK, 0 rows affected (0.25 sec)
mysql> insert into t1 values (1,'poruka1'), (2, 'poruka2'), (3, 'poruka3');
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> create table t2 (t2_id int, t1_id int, val char(10));
Query OK, 0 rows affected (0.31 sec)
mysql> insert into t2 values (1, 1, 'por1 fajl1'), (2, 1, 'por1 fajl2'),
-> (3, 1, 'por1 fajl3'), (4, 2, 'por2 fajl1'), (5, 2, 'por2 fajl2');
Query OK, 5 rows affected (0.10 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from t1 left join t2 using (t1_id);
+-------+---------+-------+------------+
| t1_id | val | t2_id | val |
+-------+---------+-------+------------+
| 1 | poruka1 | 2 | por1 fajl2 |
| 1 | poruka1 | 3 | por1 fajl3 |
| 1 | poruka1 | 1 | por1 fajl1 |
| 2 | poruka2 | 4 | por2 fajl1 |
| 2 | poruka2 | 5 | por2 fajl2 |
| 3 | poruka3 | NULL | NULL |
+-------+---------+-------+------------+
6 rows in set (0.00 sec)
ako neces poruke koje nemaju fajlove izbaci "left"
Code:
mysql> select * from t1 join t2 using (t1_id) ;
+-------+---------+-------+------------+
| t1_id | val | t2_id | val |
+-------+---------+-------+------------+
| 1 | poruka1 | 1 | por1 fajl1 |
| 1 | poruka1 | 2 | por1 fajl2 |
| 1 | poruka1 | 3 | por1 fajl3 |
| 2 | poruka2 | 4 | por2 fajl1 |
| 2 | poruka2 | 5 | por2 fajl2 |
+-------+---------+-------+------------+
5 rows in set (0.00 sec)
e sad, ako si ocekivao nesto tipa
Code:
| 1 | poruka1 | 1 | por1 fajl1 | por1 fajl2 | por1 fajl3 |
| 2 | poruka2 | 4 | por2 fajl1 | por2 fajl2 |
| 3 | poruka3 |
to generalno mozes da zaboravis posto "variabilan broj kolona" nije
nikako nesto sto rdbms voli niti ume ..
mozes da simuliras to sa group_concat
Optimizacija upita