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 | -------------------------------------------------------------------------------
完璧!