2つのテーブルを関連付けて呼び出すSQL文の書き方について

前提

よろしくお願いします。
下記2つのテーブルを設定しています。

SQL
1CREATE TABLE IF NOT EXISTS `holidaysub` (
2  `holiday_no` int(11) NOT NULL COMMENT '休日のID',
3  `holiday` varchar(50) NOT NULL COMMENT '休日',
4  `break_time` varchar(500) NOT NULL COMMENT '休み時間',
5  `shop_id` int(11) NOT NULL COMMENT '店舗ID,
6  `image_id` int(11) NOT NULL DEFAULT '0' COMMENT 'スタッフのID',
7  `created` varchar(50) NOT NULL COMMENT '作成日時'
8)

SQL
1CREATE TABLE IF NOT EXISTS `staff` (
2  `image_id` int(11) NOT NULL COMMENT 'スタッフのID',
3  `file_name` varchar(255) NOT NULL COMMENT '画像の名前',
4  `file_path` varchar(255) NOT NULL COMMENT '画像ファイルのパス',
5  `staff_name` varchar(140) NOT NULL COMMENT 'スタッフ名',
6  `shop_id` int(11) NOT NULL COMMENT '店舗ID',
7  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成日時'
8)

それぞれ「image_id(スタッフのID)」で関連づいています。

現在

PHP
1$sql = "SELECT * FROM holidaysub WHERE shop_id = ".$shop_id." AND holiday >= '{$today}' ORDER BY holiday";

のSQL文をfetch(PDO::FETCH_ASSOC)を使って、すべての行を取り出し

PHP
1 echo $rec['holiday'];

で休日を表示しています。

実現したいこと

表示している休日と関連付いた「staff」テーブルの「staff_name（スタッフ名）」を同時に

PHP
1echo $rec['holiday'].$rec['staff_name'];

このような？形で表示したいのですが、SQL文をどのように書けば良いのか分からず詰まっています。

該当のソースコード

PHP
1<?php
2session_start();
3if ( !isset( $_SESSION[ "shop_name" ] ) ) {
4  $no_login_url = "login_form.php";
5  header( "Location: {$no_login_url}" );
6  exit;
7}
8//セッションの値を受け取る
9$shop_id = $_SESSION[ 'shop_id' ];
10$shop_name = $_SESSION[ 'shop_name' ];
11$shop_mail = $_SESSION[ 'shop_mail' ];
12//日付取得
13$today = date( 'Y-m-d' ); //本日の日付
14
15require_once( '../../../core/config.php' );
16
17try {
18
19  // PDOインスタンスを生成
20  $dbh = new PDO( DSN, DB_USER, DB_PASS );
21  $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
22
23
24} catch ( PDOException $e ) {
25
26  // エラーメッセージを表示させる
27  echo 'データベースにアクセスできません！' . $e->getMessage();
28
29  // 強制終了
30  exit;
31
32}
33$sql = "SELECT * FROM holidaysub WHERE shop_id = ".$shop_id." AND holiday >= '{$today}' ORDER BY holiday";
34$prepare = $dbh->query( $sql );
35$prepare->execute();
36$count = $prepare->rowCount(); //データ数を取得
37$dbh = null;
38?>
39<!doctype html>
40<html lang="ja">
41<body>
42<!-- ++++++++++++++++++++++++ main ++++++++++++++++++++++++ -->
43<main>
44<div id="content-wrap"> 
45<!--コンテンツ開始-->
46<div id="contents">
47<div class="tableWrap_s">
48<h2>休日確認</h2>
49<?php
50if ( $count == 0 ) {
51?>
52<p class="cmt">データはありません</p>
53<?php
54} else {
55?>
56<div>
57<table>
58<tbody>
59<?php
60while ( true ) {
61  $rec = $prepare->fetch( PDO::FETCH_ASSOC );
62  if ( $rec == false ) {
63    break;
64}?>
65<tr>
66<?php if( $rec['holiday'] && empty($rec['break_time'])){?>
67<td><?php echo $rec['holiday']; ?></td>
68<td class='delete'><a href="./holidayDelete.php?no=<?php echo $rec['holiday_no']; ?>" class='submit_a'>削除</a></td>
69</tr>
70<?php }}?>
71</tbody>
72</table>
73</div>
74<?php }?>
75
76</div>
77</div>
78<!--コンテンツ終了-->
79</div>
80</main>
81<!-- ++++++++++++++++++++++++ main END ++++++++++++++++++++++++ --> 
82</body>
83</html>
84

試したこと

「CROSS JOIN」や「LEFT JOIN」を使うのかな？と考え検索したりしたのですが、実現したい事と結びつけられませんでした。

補足情報（FW/ツールのバージョンなど）

phpMyAdmin SQL
PHP Version: 5.4.16

emi_ono

2022/09/19 13:15

質問ではそれぞれ「image_id(スタッフのID)」で関連づいています。と書いたのですが、スタッフ指定がない場合holidaysubテーブルのスタッフID(image_id)に0が入るようにしたのですが、 staffテーブルのスタッフID(image_id)には0が存在しません。スタッフID(image_id)が0以外の場合は紐づいたスタッフ名を表示し、0の場合は「スタッフ指定なし」と表示させるにはSQLはどう書けば良いでしょうか？よろしくお願いします。

sazi

2022/09/20 04:44

holidaysubはスタッフ毎のテーブルですか？「スタッフ指定なし」と表示させたいのなら、holidaysubをスタッフとは関係ない状態でselectする必要がありそうですけど？

emi_ono

2022/09/21 06:48

ありがとうございます! holidaysubはスタッフ毎のページではないのですが、LEFT JOINを使ってうまくできました。

行動規範の内容に同意します

回答1件

ベストアンサー

image_id int(11) NOT NULL DEFAULT '0' COMMENT 'スタッフのID',

0に該当するスタッフIDが存在せず、必ず存在するスタッフIDがあるのなら(外部キーレベルの関連性)、LEFTじゃなくINNER JOINで良いです。

SQL
1SELECT
2    *,
3    s.staff_name
4FROM
5    holidaysub h
6   INNER JOIN
7        staff s
8    on  s.image_id = h.image_id
9

もしかしたらテーブル正規化も視野に入れた方がいいかもしれませんね。
両方のテーブルにあるshop_idとか。
今回のスタッフIDのようにNOT NULLでDEFAULT 0 というのが若干謎めいています。
必ず紐づけるなら外部キー設定にした方がいいと思います。

https://qiita.com/te2u/items/78069eefc7f12253a3ee
https://blog.senseshare.jp/database-normalization.html

投稿2022/09/19 05:35

m.ts10806

総合スコア80850

emi_ono

2022/09/19 12:40

ありがとうございます！スタッフ指定がない場合holidaysubテーブルのスタッフID(image_id)に0が入るようにしたのですが、 staffテーブルのスタッフID(image_id)には0が存在しません。ご指摘いただくまで考えていませんでした。スタッフID(image_id)が0以外の場合は紐づいたスタッフ名を表示し、0の場合は「スタッフ指定なし」と表示させたいのですが、今のデータベースの形だと無理でしょうか？教えて頂いたように $sql = " SELECT *, s.staff_name FROM holidaysub h INNER JOIN staff s ON s.image_id = h.image_id WHERE h.shop_id = ".$shop_id." AND h.holiday >= '{$today}' ORDER BY holiday "; このようにすると、0以外の値は全て期待通り表示されました。 SQL文のON s.image_id = h.image_idの下に OR (h.image_id = 0)を追記してみたのですが、、うまくできませんでした。またアドバイスを頂けると助かります。どうぞよろしくお願いします。

emi_ono

2022/09/21 06:52

もう一度整理して考えてみました。 INNER JOINをLEFT JOINに変更することで事で解決できました。テーブル正規化のアドバイスもありがとうございます。動画がすごく分かりやすかったです。