リレーショナルデータベース実験！
http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/mysql_09.htm
http://dev.mysql.com/doc/refman/4.1/ja/join.html
http://wota.jp/ac/?date=20051107
↑こちらを参考にした。
http://wota.jp/ac/?date=20060514
↑Railsと絡めるならここも役に立ちます。
●●●●準備●●●●
create database r_test;
use r_test
create table units (
id int(8),
unit_name char(8),
primary key (id)
) type=InnoDB;
create table company (
id int(8),
name char(8),
age int8,
unit_id int8,
primary key (id)
)type=InnoDB
;
create table reports (
id int(8),
company_id int(8),
content char(8),
primary key (id)
) type=InnoDB
;
insert into company values (1,'abc', 21, 1);
insert into company values (2,'def', 30, 2);
insert into company values (3,'ghi', 40, 1);
insert into reports values (1,1, 'test');
insert into reports values (2,2, 'test2');
insert into reports values (3,3, 'test3');
insert into units values (1,'aries');
insert into units values (2,'gemini');
insert into units values (3,'capu');
●●●●確認●●●●
mysql> show tables;
+------------------+
| Tables_in_r_test |
+------------------+
| company |
| reports |
| units |
+------------------+
3 rows in set (0.00 sec)
mysql> select * from units;
+----+-----------+
| id | unit_name |
+----+-----------+
| 1 | aries |
| 2 | gemini |
| 3 | capu |
+----+-----------+
3 rows in set (0.00 sec)
mysql> select * from company;
+----+------+------+---------+
| id | name | age | unit_id |
+----+------+------+---------+
| 1 | abc | 21 | 1 |
| 2 | def | 30 | 2 |
| 3 | ghi | 40 | 1 |
+----+------+------+---------+
3 rows in set (0.00 sec)
mysql> select * from reports;
+----+------------+---------+
| id | company_id | content |
+----+------------+---------+
| 1 | 1 | test |
| 2 | 2 | test2 |
| 3 | 3 | test3 |
+----+------------+---------+
3 rows in set (0.00 sec)
●●●●実験●●●●
mysql> select * from company inner join units where company.unit_id=units.id and units.id=1;
+----+------+------+---------+----+-----------+
| id | name | age | unit_id | id | unit_name |
+----+------+------+---------+----+-----------+
| 1 | abc | 21 | 1 | 1 | aries |
| 3 | ghi | 40 | 1 | 1 | aries |
+----+------+------+---------+----+-----------+
mysql> select * from reports left join company on reports.company_id=company.id;
+----+------------+---------+------+------+------+---------+
| id | company_id | content | id | name | age | unit_id |
+----+------------+---------+------+------+------+---------+
| 1 | 1 | test | 1 | abc | 21 | 1 |
| 2 | 2 | test2 | 2 | def | 30 | 2 |
| 3 | 3 | test3 | 3 | ghi | 40 | 1 |
+----+------------+---------+------+------+------+---------+
おおおぉ。分かってきた・・・。
mysql> select * from ( reports left join company on reports.company_id=company.id )
left join units on company.unit_id = units.id ;
+----+------------+---------+------+------+------+---------+------+-----------+
| id | company_id | content | id | name | age | unit_id | id | unit_name |
+----+------------+---------+------+------+------+---------+------+-----------+
| 1 | 1 | test | 1 | abc | 21 | 1 | 1 | aries |
| 2 | 2 | test2 | 2 | def | 30 | 2 | 2 | gemini |
| 3 | 3 | test3 | 3 | ghi | 40 | 1 | 1 | aries |
+----+------------+---------+------+------+------+---------+------+-----------+
おおおおぉ！完璧！
なんだ。できた。
さらに、where文も追加！
mysql> select * from ( reports left join company on reports.company_id=company.id )
left join units on company.unit_id = units.id where units.id = 1;
+----+------------+---------+------+------+------+---------+------+-----------+
| id | company_id | content | id | name | age | unit_id | id | unit_name |
+----+------------+---------+------+------+------+---------+------+-----------+
| 1 | 1 | test | 1 | abc | 21 | 1 | 1 | aries |
| 3 | 3 | test3 | 3 | ghi | 40 | 1 | 1 | aries |
+----+------------+---------+------+------+------+---------+------+-----------+
完璧！