sqlのVIEWの作り方

質問

mysqlでviewを作ろうと考えています。
書籍を調べて3日くらい格闘していましたが挫折してしまい
アドバイスをいただきたく、質問いたします。

テーブル名: kamoku
kamoku_id name
------------------
001 国語
002 算数
003 理科
004 社会

テーブル名: siken
siken_id siken_name kamoku_id1 kamoku_id2 kamoku_id3
-------------------------------------------------------
000011 試験1回目 001 002 003
000012 試験2回目　 001 003 004
000013 試験3回目 001 002 004
000014 卒業試験 003 004 null

この2つのテーブルからVIEWを作りたいと考えています

VIEW名　siken_kamoku
siken_id siken_name kamoku_id1 kamoku_id2 kamoku_id3
-------------------------------------------------------
000011 試験1回目 国語 算数 理科
000012 試験2回目　 国語 理科 社会
000013 試験3回目 国語 算数 社会
000014 卒業試験 理科 社会

科目名を一回で取得できるようなViewを作りたいのですが

CREATE VIEW view_siken_kamoku
(
siken_id,
siken_name,
kamoku_name1,
kamoku_name2,
kamoku_name3
)
AS SELECT
siken.siken_id,
siken.siken_name,
kamoku.kamoku_name,
kamoku.kamoku_name,
kamoku.kamoku_name
WHERE
siken_kamoku_id1 = kamoku.kamoku_id
AND siken_kamoku_id2 = kamoku.kamoku_id
AND siken_kamoku_id3 = kamoku.kamoku_id;

以上の文ですとエラーがでます。
どのような文にすれば、上記のようなVIEWがえられますでしょうか
おたすけてください

回答No.1

yambejp · ベストアンサー

テーブル構成が怪しいのでサンプル付きで

create table kamoku (kamoku_id int(3) zerofill,name varchar(20));
insert into kamoku values(1,'国語'),(2,'算数'),(3,'理科'),(4,'社会');
create table siken (siken_id int(6) zerofill, siken_name varchar(20), kamoku_id1 int(3) zerofill, kamoku_id2 int(3) zerofill, kamoku_id3 int(3) zerofill);
insert into siken values(11,'試験1回目',1,2,3),(12,'試験2回目',1,3,4),(13,'試験3回目',1,2,4),(14,'卒業試験',3,4,null);

そこでviewは
CREATE VIEW view_siken_kamoku(siken_id,siken_name,kamoku_name1,kamoku_name2,kamoku_name3)
AS select siken_id,siken_name,coalesce(k1.name,''),coalesce(k2.name,''),coalesce(k3.name,'')
from siken
left join kamoku as k1 on k1.kamoku_id=kamoku_id1
left join kamoku as k2 on k2.kamoku_id=kamoku_id2
left join kamoku as k3 on k3.kamoku_id=kamoku_id3;

表示は select * from view_siken_kamoku;

ところで、kamoku1～3の順番などに優劣はあるのでしょうか？
特に依存関係がないならもう少しデータをきれいに持った方が効率的だと思いますが・・・

お礼

とても早く回答いただきありがとうございます。
すぐに作業ににとりかかりたいとおもいます。
なお
VIEW名　siken_kamoku
siken_id siken_name kamoku_id1 kamoku_id2 kamoku_id3
-------------------------------------------------------
000011 試験1回目 国語 null 理科
000012 試験2回目 国語 国語　国語
000013 試験3回目 算数 null null
000014 卒業試験 null 理科　国語
000015 卒業試験 null null　null

なんでもありです。優劣もとににありません。
DBの構成があまりよくないのでしょうか。。。