複数のテーブルを結合して表示させたい

複数のオンライン診療サービスを統合した医療機関リストを作成しており、
診療科別やアプリ別、都道府県別などでもリストを作成したいと思っております。

現在のテーブルは下記のようになっております。　

create table potal (
　id int,
hosp varchar(255),　　　 医療機関名
add varchar(255),　　　　 住所
phone int,　　　　　　 電話番号
jpn text,　　　　　　　 都道府県
dep varchar(255),　　 診療科
apps varchar(255),　　 アプリ名
) without oids;

insert into potal (id, hosp, add, phone, jpn, dep, apps) values (1, '町立中標津病院', '北海道標津郡中標津町西10条南9-1-1', 0153728200, '北海道', '産婦人科,耳鼻咽喉科', 'CLINICS');
insert into potal (id, hosp, add, phone, jpn, dep, apps) values (2, 'みはら内科クリニック', '北海道函館市本通3丁目9-14', 0138845866, '北海道', '内科', 'curon');
insert into potal (id, hosp, add, phone, jpn, dep, apps) values (3, '黒川メンタルクリニック', '北海道札幌市中央区大通西18-1 道新西ビル2階', 0116329600, '北海道', '産婦人科,精神科,心療内科', 'CLINICS');
insert into potal (id, hosp, add, phone, jpn, dep, apps) values (4, '札幌西レディースクリニック', '北海道札幌市西区西町北11丁目1番5号', 0116613535, '北海道', '産婦人科', 'CLINICS')

＊例として産婦人科に絞った医療機関リストを作成したい場合、

$query="SELECT * FROM potal WHERE dep IN('産婦人科')";

先述したようにすれば、産婦人科のみを指定をすることが出来るはずなのですが、
この場合だと、四つ目のカラムしか表示されません。

おそらく、四つ目は、診療科のカラムに産婦人科しか値がないため表示することができたが、
一つ目や三つ目の場合は、診療科のカラム：　dep　に産婦人科以外の値も入っているため、
産婦人科　のみの指定が出来ないことになっています。

アドバイスとして、診療科のテーブルを作って結合するという事を教えて頂いたのですが、いまいちやり方が分からず何をして良いのか手詰まりの状態だったため質問させていただきました。

現状として、

・医療機関のテーブル
create table hospital (
　id int,
hosp varchar(255),
add varchar(255),
phone int,
jpn text
) without oids;

insert into hospital (id, hosp, add, phone, jpn) values (1, '町立中標津病院', '北海道標津郡中標津町西10条南9-1-1', 0153728200, '北海道');
insert into hospital (id, hosp, add, phone, jpn) values (2, 'みはら内科クリニック', '北海道函館市本通3丁目9-14', 0138845866, '北海道');
insert into hospital (id, hosp, add, phone, jpn) values (3, '黒川メンタルクリニック', '北海道札幌市中央区大通西18-1 道新西ビル2階', 0116329600, '北海道');

・診療科のテーブル
create table department (
id int,
dep varchar(255)
) without oids;

insert into department (id,dep) values (101, '産婦人科');
insert into department (id,dep) values (102, '内科');
insert into department (id,dep) values (103, '精神科');
insert into department (id,dep) values (104, 'リハビリテーション科');
insert into department (id,dep) values (105, '産科');
insert into department (id,dep) values (106, '循環器科');
insert into department (id,dep) values (107, '循環器内科');
insert into department (id,dep) values (108, '消化器科');
insert into department (id,dep) values (109, '小児科');
insert into department (id,dep) values (110, '耳鼻咽喉科');
insert into department (id,dep) values (111, '心療内科');
insert into department (id,dep) values (112, '婦人科');
insert into department (id,dep) values (113, '呼吸器科');

・アプリのテーブル
create table app (
　id int,
apps varchar(255)
) without oids;

insert into app (id,apps) values (01, 'CLINICS');
insert into app (id,apps) values (02, 'curon');

この三つのテーブルを作成し、結合させようと思っているのですが、
そもそも自分がやりたいことに対してこのやり方であっているのか不安です。
また、内部結合や外部結合についても少し勉強したのですが、
今の状態からどうすれば解決するのか分からず質問させていただきました。

No.2 ベストアンサー 回答者： ma0306 回答日時： 2021/01/06 01:11

①

あまり正規化を深くしなくても良いのでしたら
当初のテーブルに対して
LIKE句とワイルドカードを使った参照をしては如何でしょうか

例：
$query="SELECT * FROM potal WHERE dep LIKE '%産婦人科%' ";


②
「現状」の延長で作るのでしたらpotalテーブルや参照クエリは以下の具合になると思います

・potalテーブル
portalコード（主キー）
医療機関コード
診療科コード
アプリコード

上記の表に挿入するデータは以下の様になるかと思います
※町立中標津病院のみ例示

1101,1,101,01
1110,1,110,01

全てのデータを挿入したとして、以下のSQLで参照すれば当初想定の通りの結果となるかなと思います
※一部呼称で記載しているので、定義したカラム名に読み替えてください
※デバッグしていないですし暫く現場を離れているので、あくまでご参考までとしてください

SELECT hospital.hosp, hospital.add, hospital.phone, hospital.jpn, department.dep, app.apps FROM potal
INNER JOIN hospital
ON potal.医療機関コード = hospital.id
INNER JOIN department
ON potal.診療科コード = department.id
INNER JOIN app
ON potal.アプリコード = app.id
WHERE department.dep IN('産婦人科');

No.3 回答者： yambejp 回答日時： 2021/01/06 09:58

カンマ区切りデータはfind_in_setできます

SELECT * FROM potal
WHERE find_in_set('産婦人科',dep);

ただ最終的にプログラムで使うならdepはJSON型にして
JSON_CONTAINSで検索してもよいかもしれません

正規化するなら別途診療科テーブルをつくっておき
病院IDと診療科IDを組み合わせる中間テーブルで管理するとよいでしょう