登録されているデータにより紐づいているテーブルが変わる物のデータの取得方法が分からない。

Question

実現したいこと

teamsテーブル
| id | title | ……
| 1 | 巨人 |
| 2 | 阪神 |

usersテーブル
| id | name | age | title |……
| 1 | tanaka | 25 | ピッチャー |
| 2 | nakata | 30 | キャッチャー |

hobbby_skillsテーブル
| id | type | skill_id | hobby_id |……
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 3 | 3 | 3 | 3 |
| 4 | 4 | 4 | 4 |

hobbiesテーブル
| id | type | title |……
| 1 | 1 | 早い 　|
| 2 | 2 | 正確 　|
| 3 | 3 | 丁寧 　|　
| 4 | 4 |ピンチヒッター|

（※ここに登場するデータは仮情報なので本当のデータではありません）

分かりにくかったら申し訳ございません。
上記のテーブルで、hobby_skillsテーブルやhobbiesテーブルのtypeの値が1か3の場合、skill_idはteamsテーブルのidと紐づき、typeの値が2か4の場合、skill_idはusersテーブルのidと紐づいています。
hobby_skillsテーブルのhobby_idがhobbiesテーブルのidと紐づいています。
ここで、usersテーブルのデータをSELECT文で表示していく時に、それぞれ対応したtypeの値のtitleを同時に表示させたいです。
例えば、nakata, 30, キャッチャー, 正確……という感じで。
説明が難しいのですが、よろしくお願いいたします。

発生している問題・分からないこと

https://waterfalls.hatenablog.com/entry/2020/05/30/013149

このサイトを参考に実施してみたのですが、私の場合間に違うテーブルがあるのでうまく表示できません。

該当のソースコード

特になし

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

SELECT name,
hobbies.title AS facilities
FROM users
LEFT JOIN hobby_skills ON hobby_skills.skill_id = users.id AND hobby_skills.hobby_id = hobbies.id AND hobby_skills.type = 2, 4

全然違うと思うのですが、もうわからなさ過ぎて嫌になってきました。

補足

特になし

Answer 1

できないことはないと思いますが管理も煩雑ですし効率も悪いので、typeでこねくりまわさずに普通にテーブルをわけて正規化した方がよいでしょう。

参考

users,teams,hobbiesの独立したテーブルがあり
users=teams、users=hobbiesをリレーションさせるために中間テーブルを作るというのではればこんな感じ

create table teams(tid int primary key ,title varchar(20));
insert into teams values(1,'giants'),(2,'tigers');

create table users(uid int primary key ,name varchar(20),age int,title varchar(20));
insert into users values
(1,'tanaka',25,'pitcher'),
(2,'nakata',30,'catcher');

create table hobbies(hid int primary key,title varchar(20));
insert into hobbies values
(1,'fast'),
(2,'correct'),
(3,'carefully'),
(4,'pinch hitter');

create table user_team(uid int not null,tid int not null,primary key(uid,tid) );
insert into user_team values(1,1),(2,2);

create table user_hobby(uid int not null,hid int not null,primary key(uid,hid) );
insert into user_hobby values(1,1),(2,2);


select * from users as t1
left join user_team as t2 on t1.uid=t2.uid
left join teams as t3 on t2.tid=t3.tid
left join user_hobby as t4 on t1.uid=t4.uid
left join hobbies as t5 on t4.hid=t5.hid

Comments

[sayochika]

ご回答ありがとうございます。
その場合どのようにすればよいのでしょうか。
SQLはあまりわからなくて…。
調べてはいるのですが、理解が追いつきません。

[yambejp]

そもそもskill_idがなんなのか謎です
なにがどうリレーションしているか明確にしてください

Answer 2

(SELECT GROUP_CONCAT(title) FROM hobbies WHERE id IN (SELECT hobby_id FROM hobby_skills WHERE hobby_id = hobbies.id AND skill_id = users.id AND type = 2 OR type = 4)) AS hobby,

としたらできました。

Comments

[yambejp]

くりかえしで申し訳ないですが「できないことはない」レベルのことはやらない方がいいです

[tezcello]

> ～ AS hobby,
としているので、SELECT句の一部だと推測します。
＿＿だって、提示された中には users というテーブルは無いので
＿＿これの外側にあるのだろうと

で
SELECT users.*,
(SELECT GROUP_CONCAT(title)
FROM hobbies
WHERE id IN (
SELECT hobby_id
FROM hobby_skills
WHERE hobby_id = hobbies.id
AND skill_id = users.id
AND type = 2
OR type = 4
)
) AS hobby
FROM users;

という SQLを投げてみると
id name age title hobby
1 tanaka 25 ピッチャー ピンチヒッター
2 nakata 30 キャッチャー 正確,ピンチヒッター
という結果が得られます。
これが欲しかった結果でしょうか？
質問文からは推測が出来ませんでした。

> typeの値が2か4の場合
この条件と
AND type=2 OR type=4
とがマッチしないのでは？
多分 AND (type=2 OR type=4) の勘違い？
＿＿だから「ピンチヒッター」が出てきてしまう

> hobby_skillsテーブル（略）のtypeの値が1か3の場合、skill_idはteamsテーブルのidと紐づき
> typeの値が2か4の場合、skill_idはusersテーブルのidと紐づいています
> それぞれ対応したtypeの値のtitleを同時に表示させたい
hobby_skills.type が 1 or 3 の時は hobby_skills.skill_id は teams.id から引っ張ってきた teams.title が使われるはずでしょうが、チーム名（巨人、阪神）は「できました」で得られるデータには存在しない様です。
＿＿当方の推測した SQL が間違っている可能性はあります

tanaka と関連付くのは、hobby_skills.skill_id が users.id を指していて、それが 1 である場合です。
hobby_skills.skill_id が 1 であるレコード（hobby_skills.id=1） がは存在しますが、hobby_skills.type が 1 であるので、hobbies.id を指していますから、このレコードが usersテーブルと関連付く事は無いはずです。
＿＿少なくても提示されているデータの範囲内では

これらより、質問された内容は、解決していないはずです。
または、質問文の内容が求めているモノと合致していないと思います。

本番の（「質問用の」ではなく「本来の」）テーブル等に対する際に、本当に求めているモノであるかを十分にご確認ください。
＿＿リレーション関係をもう一度見直して、テーブル構造を再検討する必要がありそうです

Answer 3

SELECT U.*,T.*,H.*
FROM hobby_skills AS S
 LEFT JOIN teams AS T ON S.skill_id=T.id
 LEFT JOIN hobbies AS H ON S.hobby_id=H.id
 LEFT JOIN users AS U ON S.skill_id=U.id; 

とりあえず、こんなSQL（↑）で結果を取得するとこんな感じ（↓）
＿＿カラム名の重複は無視しました

id	name	age	title	id	title	id	type	title
1	tanaka	25	ピッチャー	1	巨人	1	1	早い
2	nakata	30	キャッチャー	2	阪神	2	2	正確
NULL	NULL	NULL	NULL	NULL	NULL	3	3	丁寧
NULL	NULL	NULL	NULL	NULL	NULL	4	4	ピンチヒッター

例えば、nakata, 30, キャッチャー, 正確……という感じで。

この目標だけなら達成できていますが、何が本当にやりたいのかを質問文から読み取れませんでした。
hobby_skills.typeの違いによってtitleとして取得したい値が hobbies.title であるか、teams.title であるのかが切り替えたいのなら

SELECT U.*,
	CASE
	  WHEN S.type IN (1,3) THEN T.title
      WHEN S.type IN (2,4) THEN H.title
      ELSE NULL
    END title
FROM hobby_skills AS S
LEFT JOIN teams AS T ON S.skill_id=T.id
LEFT JOIN hobbies AS H ON S.hobby_id=H.id
LEFT JOIN users AS U ON S.skill_id=U.id;

の様に SELECT句を書き換えれば、こんな感じに取得できます。

id	name	age	title	title
1	tanaka	25	ピッチャー	巨人
2	nakata	30	キャッチャー	正確
NULL	NULL	NULL	NULL	NULL
NULL	NULL	NULL	NULL	ピンチヒッター

＿＿カラム名が重複するけれど、何が適切なのか判らないのでそのままにした

説明が難しい

やりたい事を丁寧に説明してもらえないと、回答者は何ともならないです...
テーブル名もカラム名も内容も、全て質問用のテキトーなモノでしょうが、もう少し理解し易いモノを選んで頂けると嬉しいです。

hobby_skillsテーブルやhobbiesテーブルのtypeの値が

hobby_skills.type は先述の様に使いましたが、hobbies.type の使い道が解りませんでした。

usersテーブルのデータをSELECT文で表示していく時に、それぞれ対応したtypeの値のtitleを同時に表示させたい

提示されたデータが悪いのか、当方の理解が悪いのか...
巨人に所属するuserを取得しようとしても、tanakaさんは得られないけれど、それが仕様に合っているのかは不明...

Answer 4

Joinする対象のテーブルに関してunionでteamsとusersを重ねたテーブルを作って、1,3と2,4を持たせるとか1,3は1で2,4は2に変換する分岐とか、まあそんな感じでやれば行けるとは思いますが、ベストかどうかは分かりません。
もしかしたらシンプルにjoinだけでできるのかもしれないですが、頭の中だけだとどんな列になるのか想像がつきません。テーブルが別なので無理な気もしますが(nullなら変換するとかをSELECTなどの参照の方で書けば行けるとは思うが)

Unionはサブクエリにて行う想定です。

今回の場合どうなるか
SQLはデータをレコードでしか取れないので、どういうレコードが欲しいのか？を先ず考えて、データの形式(列)で表せるか考えます。パターンが違い物理的に表現不可能なら取得できないデータということになります。

この場合、もとのIDという列とtitleという列が必要そうですよね。
また、1,2の分岐用の仮想的な列も必要そうです。

Joinする時にどのような行が必要かをonで指定します。

まずはjoinする前のデータをテーブル形式で抽出して見ると良いかと思います

Comments

[sayochika]

unionについて調べてみましたが今回の場合だとどうなるのでしょうか。
もう頭が働きません。