前提・実現したいこと
MySQL5.1.73を使用しています。
社員情報を閲覧できるWebアプリを開発しております。
社員の代表部署を取得するSQLを組んでいますが、
いまいち実現できておりません。
社員テーブル、社員部署テーブル、部署マスタのテーブルが存在します。
1社員は部署を兼任できるものとします。
社員テーブル
・ID
・社員ID
・その他省略
社員部署テーブル
・ID
・社員ID
・部署ID
・その他省略
部署マスタテーブル
・ID
・部署ID
・並び順
・その他省略
社員テーブルを基にして社員部署テーブル、部署マスタをJoinし社員情報を取得しています。
社員1人に対し複数部署を設定できるため、複数部署が設定済みの場合、
当該社員の代表部署を部署マスタの並び順カラムを参照し、
並び順カラムの数字が小さい(昇順)部署を代表部署として表示するようにしたいと考えております。
実行SQL
※端折っていますがサブクエリを使用する必要があります
SELECT 社員情報.社員ID, 社員部署情報.部署ID, 社員部署情報.並び順 FROM 社員情報 LEFT OUTER JOIN( SELECT 社員ID, 部署ID FROM 社員部署情報 LEFT OUTER JOIN 部署マスタ ON (社員部署情報.部署ID = 部署マスタ.部署ID) ) AS `test` ON (社員情報.社員ID = test.社員ID) GROUP BY 社員情報.社員ID ORDER BT 社員情報.社員ID asc;
わからないこと
GROUP BYをすると意図しない部署IDを取得してしまいます。
MySQLのリファレンスや似たような事例(https://oshiete.goo.ne.jp/qa/6924438.html)等を
参考にしてみましたが、わかりませんでした。
PHPで配列等使って...と色々とやりようはありますが、
SQLだけで実現できるのかをご教示いただきたいです。
例えば。。
社員ID:1のAさんに紐づいている社員部署テーブルには
ID:1, 社員ID:1, 部署ID:3 ID:2, 社員ID:1, 部署ID:2 ID:3, 社員ID:1, 部署ID:1
というデータがあったとして、部署マスタテーブルには
ID:1, 部署ID:3, 並び順:3 ID:2, 部署ID:2, 並び順:1 ID:3, 部署ID:1, 並び順:2
というデータがあったとします。
社員ID:1のAさんの代表部署は「部署ID:2」なのですが、
現在は「部署ID:3」を代表部署として取得してしまいます。
(いまいち仕様を理解できていないのですが、GROUP BYは最初に見つかったレコードでグルーピングするのでしょうか。。)
色々と端折っているところもあり、伝わりにくいかもしれませんが
何卒ご教授いただけますと幸いです、よろしくお願いいたします。
追記(2019/03/12 19:15〜)
テストデータは以下のとおりです。
-- 社員テーブル CREATE TABLE `m_test_employees` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `emoloyee_id` int(10) NOT NULL, `name` varchar(255) NOT NULL DEFAULT '', `delete_flag` tinyint(3) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 社員部署テーブル CREATE TABLE `m_test_employee_divs` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `employee_id` int(10) NOT NULL, `div_id` int(10) NOT NULL, `delete_flag` tinyint(3) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 部署マスタテーブル CREATE TABLE `m_test_div_master` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `div_id` int(10) NOT NULL, `order` int(2) NOT NULL DEFAULT '1', `delete_flag` tinyint(3) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 各種データインサート INSERT INTO `m_test_employees` (`id`, `emoloyee_id`, `name`, `delete_flag`) VALUES (1,1,'Aさん',0), (2,2,'Bさん',0), (3,3,'Cさん',0); INSERT INTO `m_test_employee_divs` (`id`, `employee_id`, `div_id`, `delete_flag`) VALUES (1,1,3,0), (2,1,2,0), (3,1,1,0); INSERT INTO `m_test_div_master` (`id`, `div_id`, `order`, `delete_flag`) VALUES (1,3,3,0), (2,2,1,0), (3,1,2,0);
動作確認SQL
SELECT employee_id, test.div_id, test.order FROM m_test_employees LEFT OUTER JOIN ( SELECT m_test_employee_divs.employee_id, m_test_employee_divs.div_id, m_test_div_master.order FROM m_test_employee_divs LEFT OUTER JOIN m_test_div_master ON (m_test_employee_divs.div_id = m_test_div_master.div_id) ) AS test ON (employee_id = test.employee_id) GROUP BY employee_id ORDER BY employee_id asc;
更に追記(2019/03/12 20:00〜)
INSERT INTO `m_test_employee_divs` (`id`, `employee_id`, `div_id`, `delete_flag`) VALUES (4,2,3,0), (5,2,2,0), (6,3,1,0);
を更にインサートします。
社員ID:1のAさんの代表部署は「部署ID:2」
社員ID:2のBさんの代表部署は「部署ID:2」
社員ID:3のCさんの代表部署は「部署ID:1」
としたいです。
補足情報(FW/ツールのバージョンなど)
PHP:7系
Fuelphp:1.9
回答2件
あなたの回答
tips
プレビュー