カテゴリー
SQL インターネット一般 (Internet General) プログラミング (Programming)

三つのテーブルをつなぐ肝腎の SQL文

忘れない内に残しておきましょう というのも、インターネット上には誤った SQL文が堂々と upload公開されていたりするのですから・・・

まずはテーブルの定義です

CREATE TABLE `man_tbls` (
	`id` INT( 11 ) NOT NULL AUTO_INCREMENT,
	`sirname` VARCHAR( 100 ) NOT NULL DEFAULT '',
	`firstname` VARCHAR( 100 ) NOT NULL DEFAULT '',
	`is_male` BOOLEAN NOT NULL DEFAULT '1',
	`birthdate` DATE NOT NULL DEFAULT '0000-00-00',
	PRIMARY KEY (`id`),
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci AUTO_INCREMENT=0;

CREATE TABLE `disease_tbls` (
	`id` INT( 11 ) NOT NULL AUTO_INCREMENT,
	`diagnosis` VARCHAR( 100 ) NOT NULL DEFAULT '',
	PRIMARY KEY (`id`),
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci AUTO_INCREMENT=0;

CREATE TABLE `relation_tbls` (
	`id` INT( 11 ) NOT NULL AUTO_INCREMENT,
	`man_tbl_id` INT( 11 ) NOT NULL DEFAULT '0',
        `disease_tbl_id` INT( 11 ) NOT NULL DEFAULT '0',
	PRIMARY KEY (`id`),
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci AUTO_INCREMENT=0;

この SQL文により、MySQLデータベース・エンジンにより三つのテーブルが作成されます 本当はこのデータベースに対して、さっき記述したデータを入力しておいて下さると助かりますが・・・

さて、このようなデータベース、これは実は 二つの固有テーブルを「関係テーブル」で結びつける「多」対「多」関係のデータなのですが、この中から、ある人が、ある病気にかかっている、ということを記述する SQL文は次のようになります

SELECT `M`.`id`,  `M`.`sirname`, `M`.`firstname`, `M`.`is_male`, `M`.`birthdate`,`D`.`disease
FROM `man_tbls` AS `M` INNER JOIN(`relation_tbls` AS `R` INNER JOIN  `disease_tbls` AS `D` ON `D`.`id` = 
`R`.`disease_tbl_id`  ) ON `M`.`id` = `R`.`man_tbl_id`;

結構複雑でしょ?

カテゴリー
SQL ただの現状記述 (Daily Activities) アメリカ (USA) インターネット一般 (Internet General) コンピューター (Computer) プログラミング (Programming) 台湾 (Taiwan) 外国訪問 (Foreign Visits)

少し複雑な SQL文

一泊二日で台湾・高雄でお仕事して月曜日は外来診療、そして色々なお仕事しました

火曜日の9:27は朝から Houstonに向けて飛び立ち、そのまま Colombiaに入るのです 今は、Houston空港で 6時間余りの transitをしています

そんな中懸案の SQLがようやく書けました これは、三つのテーブルを結びつけるものですが、要するに「多」対「多」という二つのテーブルを結びつけるために、間に「関連」テーブルを使うものです

この関係は実社会で良く見られるものです 例えば 人物テーブルを考えて下さい、このテーブルで重要なデータは

  1. 固有のID 今日の日本であれば、「個人番号」でしょうね
  2. 名前の姓
  3. 名前の名
  4. 性別
  5. 生年月日

などでしょう もちろん他にも色々付け足せますが、ここで重要なのは個々のフィールド (ここでは番号の1~5ですが)については、一度値が入れば、変化しない、ということです そして、1については、「個人番号」がそうであるように、重複することが決して無い ということなのですよっ

例えば、6.として「職業」を持ってきたとします ある時点では、その値は、学生であり、次には会社員となり、その次にはアルバイトになるかも知れません 要するに、「職業」というものは変化していきますので、この人物テーブルの構成要素としては相応しくありません

もちろん、「職業1」「職業2」「職業3」・・・・「職業10」ぐらい予備に造っておけばそれでも良いでしょうが

名前については、実は微妙な問題があります 日本では結婚することにより女性の姓が変化するのが普通ですので、最初から「姓1」「姓2」・・「姓5」ぐらい造っておいた方が良いかも知れませんね

さて、次に 病気テーブル について考えましょう これは簡単に次のようにしましょう

  1. 病気のID ここは例えば色々な国際的コードがあります
  2. 病名 例えば、胃炎、心筋梗塞などなどですね

さて、この二つのテーブルを用いて、ある人がある病気にかかっている という状態をどのように表せば良いでしょうか?

実はここで必要なのが、二つのテーブルを結びつける「関係テーブル」なのです

まずは「人物テーブル」です

ID 性別 生年月日
1 ABE SHI M 19500215
2 TABE SHISHI F 19511115
3 TABEKI MOMO M 19520415

さて次に「病気テーブル」です

ID 病名
1 急性胃炎
2 慢性胃炎
3 狭心症

さて、ある人の状態を表すにはどのようにすれば良いでしょうか? 例えば TABEさんが狭心症にかかっている、というのは「人物テーブル」のID=1と 「病気テーブル」のID3を結びつければ良いですよね 従って以下の 関連テーブルが必要となります

ID 人物テーブルID 病気テーブルID
1 1 3

このテーブルをどんどん増やしていけば良いのですよね これが Relational Database (関係データベース)の基礎の基礎なのですが、結構難しいのですよ これをSQL文で書くのは