テーブル設計と要件を満たすカラム指定の問題

Question

・火曜日2限に開講されている授業一覧
を表示させるために下記のテーブルを設定し、

create table classes (
  id int primary key auto_increment,
  name text
);

insert into classes (id,name) values (1,'IT');
insert into classes (id,name) values (2,'Math');

create table weekdays (
  weekday_id int primary key auto_increment,
  weekday_name text,
  id int,
  foreign key (id) references classes (id)
);

insert into weekdays (weekday_id,weekday_name) values (1,'Sunday');
insert into weekdays (weekday_id,weekday_name) values (2,'Monday');
insert into weekdays (weekday_id,weekday_name) values (3,'Tuesday');

create table classtimes (
  classtime_id int,
  id int,
  foreign key (id) references classes (id),
  weekday_id int,
  foreign key (weekday_id) references weekdays (weekday_id)
);

insert into classtimes (id,classtime_id,weekday_id) values (1,1,1);
insert into classtimes (id,classtime_id,weekday_id) values (1,2,1);
insert into classtimes (id,classtime_id,weekday_id) values (1,3,1);
insert into classtimes (id,classtime_id,weekday_id) values (2,1,2);
insert into classtimes (id,classtime_id,weekday_id) values (2,2,2);
insert into classtimes (id,classtime_id,weekday_id) values (2,3,2);
insert into classtimes (id,classtime_id,weekday_id) values (2,1,3);
insert into classtimes (id,classtime_id,weekday_id) values (2,2,3);
insert into classtimes (id,classtime_id,weekday_id) values (2,3,3);

下記のsql文を書きましたが、

select classes.name from classtimes
inner join classes on classtimes.classtime_id = classes.id
inner join weekdays on classtimes.classtime_id = weekdays.weekday_id
where weekdays.weekday_id = 3;

下記のようにエラーが発生してしまいます。

+------+
| name |
+------+
| Math |
| Math |
| Math |
+------+

うまく出力されない理由として、

・classtimeテーブルのinsert文がおかしい

ということは考えついたのですが、具体的にどう直せばいいのか分かりません。訂正していただけないでしょうか？

よろしくお願いお願いいたします。

=======
修正：
weekdaysテーブルとclasstimesテーブルの順番がおかしかったので訂正しました。
エラー内容はEmpty setです。

Comments

[KiyoshiMotoki]

本当にこの順番でSQLを実行しましたか？
classtimesテーブルはweekdaysテーブルを参照しているので、
weekdaysテーブルより先にclasstimesテーブルを作成することはできないはずです。

また、単に
「うまく出力されません」
ではなく、どのような現象が起きる（または、起きない）のかを詳しく記載して下さい。
エラーメッセージが表示されるのであれば、それも添付して下さい。

[Kei227]

編集し直しました。

Answer 1

ご質問の内容からは2限を表現するデータがどれなのかが読み取れませんでしたが、
とりあえずclasstimesテーブルのclasstime_idカラムだと仮定して回答させていただきます。

正しいSQL文は

SELECT classes.name 
FROM classes 
INNER JOIN classtimes 
    ON classes.id = classtimes.id 
WHERE classtimes.weekday_id = 3 
    AND classtimes.classtime_id = 2;

または

SELECT classes.name 
FROM classes 
INNER JOIN classtimes 
    ON classes.id = classtimes.id 
INNER JOIN weekdays 
    ON classtimes.weekday_id = weekdays.weekday_id 
WHERE weekdays.weekday_name = 'Tuesday' 
    AND classtimes.classtime_id = 2;

です。

Kei227様のSQL文が意図通りの結果を返さない原因は、テーブル結合（INNER JOINの部分のことです）の条件付けを間違えているからです。
1つ目の結合条件ですが、classesテーブルで"授業"を一意に識別するカラムはidで、classtimesテーブルでclasses.idを参照しているカラムもidなので、

on classtimes.id = classes.id

とすべきでした。

2つ目の結合条件については、そもそもweekdaysテーブルの結合条件に同テーブルを使用していないですね。
weekdaysテーブルで"曜日"を一意に識別するカラムはweekday_idなので、1つ目の結合条件と同様に

on classtimes.weekday_id = weekdays.weekday_id

とすべきでした。

結果的に意図するデータを取得できているとしても、これではデータの内容や条件が変更されると、再び意図通りに動かなくなりますよ。

あと、weekdaysテーブルにidカラムは不要です。
"曜日"と"授業"を紐づける役割はclasstimesテーブルが担っているので、
weekdaysテーブルが直接、classesテーブルを参照する必要はないためです。

Comments

[Kei227]

詳しい解説のほど、ありがとうございます！！

[Kei227]

https://teratail.com/questions/19527?complete=
よろしかったらこちらの問題も確認していただけないでしょうか？

[KiyoshiMotoki]

嫌ですw

理由は
https://teratail.com/questions/19527
の追加・修正依頼に記載させていただきました。

Answer 2

最後に【limit1】をつけることで解決できました。

select classes.name from classtimes
inner join classes on classtimes.classtime_id = classes.id
inner join weekdays on classtimes.classtime_id = classes.id
where weekdays.weekday_id = 3 and classtime_id = 2 limit 1;

Comments

[KiyoshiMotoki]

このSQL文は誤っています。
回答に詳しい説明をさせていただきますので、ご確認ください。

Answer 3

classtimesのテーブルで、classtime_id int primary key auto_incrementとプライマリーキーとして使ってしまっているので、これをclasses.idと結びつけようとしてもまったく無関係なものとなってしまい、うまくいきません。

classtimes.idがclasses.idと外部キーで結ばれているので、INSERT時にclasstimes.idに適切な値を入れて、こちらでJOINしましょう。

Comments

[Kei227]

classtime_idのprimary keyを外し、insert文を下記のように編集したのですがEmpty setと出てしまいます。

==========
insert into classtimes (classtime_id,weekday_id) values (1,1);
insert into classtimes (classtime_id,weekday_id) values (2,1);
insert into classtimes (classtime_id,weekday_id) values (3,1);
insert into classtimes (classtime_id,weekday_id) values (4,1);
insert into classtimes (classtime_id,weekday_id) values (5,1);
insert into classtimes (classtime_id,weekday_id) values (1,2);
insert into classtimes (classtime_id,weekday_id) values (2,2);
insert into classtimes (classtime_id,weekday_id) values (3,2);
insert into classtimes (classtime_id,weekday_id) values (4,2);
insert into classtimes (classtime_id,weekday_id) values (5,2);
insert into classtimes (classtime_id,weekday_id) values (1,3);
insert into classtimes (classtime_id,weekday_id) values (2,3);
insert into classtimes (classtime_id,weekday_id) values (3,3);
insert into classtimes (classtime_id,weekday_id) values (4,3);
insert into classtimes (classtime_id,weekday_id) values (5,3);
==========

*月曜日の1~5限、火曜日の1~5限のようなことを意図して書いています。

[maisumakun]

1コマの授業を定義する上で、曜日・時間・科目の3つ組が必要だと思うのですが…上のINSERTで「科目」は指定もしていないですよね？