前回記事MySQLで外部結合と内部結合1～他テーブルのフィールド情報変更に対応する～では二つのテーブルの結合について考えていきました。今回は3つのテーブルの結合について考えていきたいと思います。10月15日記事
目次
色々なSNSサイトでよくファンや友達になっている人が記事やブログを書いた時に紹介される機能があります。このような場合、アカウントテーブル、記事テーブル、フレンドテーブルの3つのテーブルを結合している場合が多いです。今回、テーブル構造及びクエリの投げ方についてまとめていきたいと思います。
CREATE TABLE account( account_id INT NOT NULL AUTO_INCREMENT, account_nickname VARCHAR(255) NOT NULL, account_passwordsalt VARCHAR(255) NOT NULL, account_status TINYINT NOT NULL, PRIMARY KEY(account_id) );
※statusは1登録中 2は退会済みを表す。
データ登録 password_saltはpasswordという文字列にhogeを加えたもの passwordhogeをmd5化。
INSERT INTO account (account_nickname,account_passwordsalt,account_status) VALUES ('織田信長','621f4bf792982670d1c42988c1495a4b',1);
INSERT INTO account (account_nickname,account_passwordsalt,account_status) VALUES ('豊臣秀吉','621f4bf792982670d1c42988c1495a4b',1);
INSERT INTO account (account_nickname,account_passwordsalt,account_status) VALUES ('徳川家康','621f4bf792982670d1c42988c1495a4b',1);
INSERT INTO account (account_nickname,account_passwordsalt,account_status) VALUES ('明智光秀','621f4bf792982670d1c42988c1495a4b',1);
INSERT INTO account (account_nickname,account_passwordsalt,account_status) VALUES ('武田信玄','621f4bf792982670d1c42988c1495a4b',1);
INSERT INTO account (account_nickname,account_passwordsalt,account_status) VALUES ('上杉謙信','621f4bf792982670d1c42988c1495a4b',1);
CREATE TABLE article( article_id INT NOT NULL AUTO_INCREMENT, article_author_id INT NOT NULL, article_title VARCHAR(255) NOT NULL, article_comment text NOT NULL, article_status TINYINT NOT NULL, PRIMARY KEY(article_id) );
※statusは1が公開中、2は削除済を表す。
データ登録
INSERT INTO article(article_author_id,article_title,article_comment,article_status) VALUES(1,'タイトル１','コメント1',1); INSERT INTO article(article_author_id,article_title,article_comment,article_status) VALUES(1,'タイトル２','コメント2',1); INSERT INTO article(article_author_id,article_title,article_comment,article_status) VALUES(3,'タイトル３','コメント3',1); INSERT INTO article(article_author_id,article_title,article_comment,article_status) VALUES(5,'タイトル４','コメント4',1); INSERT INTO article(article_author_id,article_title,article_comment,article_status) VALUES(5,'タイトル５','コメント5',2); INSERT INTO article(article_author_id,article_title,article_comment,article_status) VALUES(5,'タイトル６','コメント6',1);
ややこしいので整理
織田信長は記事を二つ書いている。 徳川家康は記事を一つ書いている。 武田信玄は記事を三つ書いているが一つは削除された。
CREATE TABLE fun( fun_id INT NOT NULL AUTO_INCREMENT, fun_player_id INT NOT NULL, fun_friend_id INT NOT NULL, fun_status TINYINT NOT NULL, PRIMARY KEY(fun_id) );
※statusは1がfun中,2がfun解除状態を表す。
データ登録
INSERT INTO fun(fun_player_id,fun_friend_id,fun_status) VALUES(1,2,1); INSERT INTO fun(fun_player_id,fun_friend_id,fun_status) VALUES(1,3,1); INSERT INTO fun(fun_player_id,fun_friend_id,fun_status) VALUES(1,4,1); INSERT INTO fun(fun_player_id,fun_friend_id,fun_status) VALUES(1,5,1); INSERT INTO fun(fun_player_id,fun_friend_id,fun_status) VALUES(1,6,1); INSERT INTO fun(fun_player_id,fun_friend_id,fun_status) VALUES(2,1,1); INSERT INTO fun(fun_player_id,fun_friend_id,fun_status) VALUES(2,3,2); INSERT INTO fun(fun_player_id,fun_friend_id,fun_status) VALUES(3,1,1); INSERT INTO fun(fun_player_id,fun_friend_id,fun_status) VALUES(3,2,1); INSERT INTO fun(fun_player_id,fun_friend_id,fun_status) VALUES(3,5,1); INSERT INTO fun(fun_player_id,fun_friend_id,fun_status) VALUES(3,6,1); INSERT INTO fun(fun_player_id,fun_friend_id,fun_status) VALUES(4,1,2); INSERT INTO fun(fun_player_id,fun_friend_id,fun_status) VALUES(4,2,2); INSERT INTO fun(fun_player_id,fun_friend_id,fun_status) VALUES(4,3,2); INSERT INTO fun(fun_player_id,fun_friend_id,fun_status) VALUES(4,5,2); INSERT INTO fun(fun_player_id,fun_friend_id,fun_status) VALUES(4,6,2);
ややこしいので、整理
1織田信長がfunなのは 自分以外の全員 2豊臣秀吉がfunなのは 織田信長 3徳川家康がfunなのは 織田信長、豊臣秀吉 武田信玄 上杉謙信 4明智光秀がfunなのは いない。
三つ以上結合して、かつ自分のfunのステータスが1の状態で、投稿状態が1のものでニックネームも取得する。
//織田信長がfunになっている人の記事 SELECT * FROM article JOIN fun ON article.article_author_id = fun.fun_friend_id JOIN account ON article.article_author_id = account.account_id WHERE article_status = 1 AND fun_status = 1 AND fun_player_id = 1; //豊臣秀吉がfunになっている人の記事 SELECT * FROM article JOIN fun ON article.article_author_id = fun.fun_friend_id JOIN account ON article.article_author_id = account.account_id WHERE article_status = 1 AND fun_status = 1 AND fun_player_id = 2; //明智光秀がfunになっている人の記事 SELECT * FROM article JOIN fun ON article.article_author_id = fun.fun_friend_id JOIN account ON article.article_author_id = account.account_id WHERE article_status = 1 AND fun_status = 1 AND fun_player_id = 4; //その他ワイルドカード以外で取得するカラムを指定して取得する SELECT account.account_id,article.article_title,article.article_id FROM article JOIN fun ON article.article_author_id = fun.fun_friend_id JOIN account ON article.article_author_id = account.account_id WHERE article_status = 1 AND fun_status = 1 AND fun_player_id = 1
色々とクエリをいじってみてください。
結合する（紐づけする）ものが同じ意味を持つものであればJOINを二回することで結合ができます。また、このような場合、INDEXを付け忘れることも忘れずに行ってください。
CREATE INDEX インデックス名 ON テーブル名 (カラム名[, カラム名…]) or ALTER TABLE テーブル名 ADD INDEX [インデックス名] (カラム名[, カラム名…])
で対応できます。それぞれのテーブルの必要な箇所に張っておきましょう。
以上
