SQL・結合方法

質問

最近、本やインターネットの情報を見ながらMysqlの勉強をしています。
どうしても分からないことがあるので教えてください。

とある講義とその講師に関するデータベースを作成しました。
「正規化」というものがあると知りいろいろ参考にしつつ以下のようなテーブルを作りました。

講義テーブル
日付　｜　テーマ　｜　講師（ID）　｜　講師2（ID）　｜

講師テーブル
講師ID　|　講師名　｜　備考　｜

この状態で2つのテーブルを結合して表示させる方法がわかりません。
例えば、1つの講義に1人の講師しかつかない（講師2という列がない）場合は
select　*　from　講義・講義テーブル
　where　講義tbl.講師ID　=　講師tbl.講師ID　;
とすればうまく表示できるのはわかるのですが

はじめにあげたように複数の講師がつき、同じテーブルを参照したい場合はどうすればよいのでしょうか？

初歩的な質問かもしれませんが、宜しくお願いいたします。

回答No.5

TagoSuck · ベストアンサー

みなさんが書かれている事ですが、講師テーブルは実際にはテーブルとして、ひとつだけですが、このテーブルを二度、別の物として利用するということですね。

誤解を恐れずに書けば、講師テーブルのコピーを作成して、講義テーブルに対して、オリジナルの講師テーブルとコピーの講師テーブルの両方を結合すれば、講義tbl.講師はオリジナルの講師テーブルの講師IDから、講義tbl.講師2はコピーの講師テーブルから取得できます。

ただ、この時に同じ「講師」というテーブル名ではどっちのテーブルから参照して良いのかがわからないので、それぞれの講師テーブルにニックネームを付けてやります。講師オリジナルテーブル、講師コピーテーブルのように。
この部分がASを使って書かれている部分です。
講師テーブル AS 講師オリジナルテーブル
という感じで、テーブルの別名を指定します。

ご質問から、同じテーブルを複数回参照する方法をお知りになりたいかと思いますので、AS を使って別名を付ける事で、同じテーブルを別の名前で利用するという回答になるでしょうか。

補足

お礼が遅くなり申し訳ありません。

TagoSuckさんの解説でやっと仕組みが理解できました！
複数回参照には別の名前をつける必要があったんですね。

ありがとうございました！

回答No.4

tierlieb

MySQLの実行環境がないため、明言はできませんが。。。

SELECT　*　
FROM　
　　講義テーブル,
　　講師テーブル　T1,　　--　←講義テーブル．講師ID用
　　講師テーブル　T2　　 --　←講義テーブル．講師ID2用
WHERE
　　講義テーブル．講師ID　=　T1.講師ID
AND
　　講義テーブル．講師ID2　=　T2.講師ID；

とすればOracleなどでは取得できますが。。。MySQLではいかがでしょうか？

お礼

MySQLの環境でも上記の方法で取得できました！

left join等のコマンドとの違いがいまいち理解していないのですが
そのあたりは今後理解していこうと思います。

回答No.3

yambejp

正規化するのであれば、

講義テーブル
日付　｜　テーマ　｜　講師（ID）　｜　講師2（ID）　｜
はまずいのでは？

講義テーブル
講義ID　|　日付　｜　テーマ　｜

講義講師テーブル
講義ID　|　講師ID　|

として分離して管理すべきだとおもいます。
（講師１，２を並列で表示するには若干工夫が必要になりますけど）

お礼

ご指摘ありがとうございます。

まだ正規化が足りないことにはきづいていたんですが、
講師が三人以上になることはまず無いので
今回は分離せずに作成する形をとりました。

ただ、今後の勉強の上でご指摘の方法も検討していこうと思います。

回答No.2

chukenkenkou

講義tblと講師tblをleft join、さらに講師tblをleft joinします。

create table 講義tbl
(日付 date,
テーマ varchar(15),
講師id smallint,
講師id2 smallint);
create table 講師tbl
(講師id smallint,
講師名 varchar(15),
備考 varchar(30));

insert into 講師tbl values
(1,'川口',null),
(2,'楢崎',null),
(3,'宮本',null),
(4,'中澤',null),
(5,'稲本',null),
(6,'中田',null),
(7,'高原',null);
insert into 講義tbl values
('2007-03-15','GK論',1,2),
('2007-03-15','DFK論',3,4),
('2007-03-15','トルコ語',5,null),
('2007-03-15','イタリア語',6,null),
('2007-03-15','ドイツ語',7,null),
('2007-03-15','攻撃について',7,6),
('2007-03-15','守備について',4,3);

select 日付,テーマ,y.講師名,z.講師名
from 講義tbl as x
left join 講師tbl as y
on x.講師id=y.講師id
left join 講師tbl as z
on x.講師id2=z.講師id
;

お礼

とても丁寧なご解答ありがとうございました！

やっと理解できました！

回答No.1

jiji1954

表名や列名が曖昧なのが、ちょっと「？」ですが、表名称および列名を以下のようにするとしたら、
CREATE TABLE [T_instructor] (
[ID] [nvarchar] (50) COLLATE Japanese_CI_AS NOT NULL ,
[name] [nvarchar] (50) COLLATE Japanese_CI_AS NULL ,
[memo] [nvarchar] (50) COLLATE Japanese_CI_AS NULL ,
CONSTRAINT [aaaaaT_instructor_PK] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [T_lecture] (
[ID] [smallint] NOT NULL CONSTRAINT [DF__T_lecture__ID__76CBA758] DEFAULT (0),
[theme] [nvarchar] (50) COLLATE Japanese_CI_AS NULL ,
[instructor1] [nvarchar] (50) COLLATE Japanese_CI_AS NULL ,
[instructor2] [nvarchar] (50) COLLATE Japanese_CI_AS NULL ,
CONSTRAINT [aaaaaT_lecture_PK] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

SELECT dbo.T_lecture.ID, dbo.T_instructor.name, T_instructor_1.name
FROM (dbo.T_lecture INNER JOIN dbo.T_instructor ON dbo.T_lecture.instructor1=dbo.T_instructor.ID)
INNER JOIN dbo.T_instructor AS T_instructor_1 ON dbo.T_lecture.instructor2=T_instructor_1.ID;

補足

詳しい解説ありがとうございます。

ただちょっと難しくて理解できません・・・。

selectの部分、特に理解できてないのですが
T_instructor_1というのはテーブル名を指定しているのでしょうか？
そうなるとT_lecture・T_instructor・T_instructor_1という
３つのテーブルが存在することになるのですが・・
それとも何か別の意味合いを持っているのでしょうか？