MySQL JOIN構文

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

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

完璧!