プログラマ 福重 伸太朗 ～基本へ帰ろう～

リレーショナルデータベース実験！

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     |
+----+------------+---------+------+------+------+---------+------+-----------+

完璧！