SQLで複雑なリレーションシップを検索する (Re: Laravelで複雑なリレーションシップの検索をシンプルにする)

SUKOHI 氏がご自身のブログでSQLを募集していたので、実装してみました。

お題

Laravelで複雑なリレーションシップの検索をシンプルにする – console dot log
https://blog.capilano-fw.com/?p=4748

ただ、そんな状況であってもこの間、「うーーーーーん・・・」が発生したことがありました。
それは、例えば、以下のようなデータベースの検索です。

hasManyで結合した子テーブルにはデータが入っていないもの（ただし、バナナはカウントから除外する）を取得する

hasManyした子テーブルの「name」が「イチゴ」だけのもの（ただし、パイナップルはカウントから除外する）を取得する

（中略）
もし、SQL一発で実行可能な方法を知っていたら、ぜひお問い合わせフォームから教えてください！

動作確認環境

MySQL Online - Paiza.io
https://paiza.io/en/languages/mysql
select version(); -> 5.7.26-0ubuntu0.18.04.1

テストデータの作成

CREATE TABLE `box` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NULL COMMENT '箱名',
  PRIMARY KEY (`id`)
);

CREATE TABLE `box_detail` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `box_id` int(10) unsigned NOT NULL,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NULL COMMENT '品名',
  PRIMARY KEY (`id`)
);

INSERT INTO box (name) VALUES 
('箱１（空）')
,('箱２（バナナ）')
,('箱３（バナナりんご）')
,('箱４（NULL）')
,('箱５（いちご）')
,('箱６（いちごパイナップル）')
,('箱７（いちごパイナップルりんご）')
;

INSERT INTO box_detail (box_id,name) VALUES 
(2,'バナナ')
,(2,'バナナ')
,(3,'バナナ')
,(3,'バナナ')
,(3,'りんご')
,(4, NULL)
,(5,'いちご')
,(5,'いちご')
,(6,'いちご')
,(6,'パイナップル')
,(6,'いちご')
,(7,'いちご')
,(7,'パイナップル')
,(7,'りんご')
;

問１

hasManyで結合した子テーブルにはデータが入っていないもの（ただし、バナナはカウントから除外する）を取得する

boxのレコードそれぞれに対して「バナナでないbox_detailの親box_id」をjoinして、box_detailの親box_idがない(nullである)ようなboxを選択する。

select
 box.name, box.id
 -- , t.box_id
from box
left outer join (select distinct box_id from box_detail where name is null or name <> 'バナナ') as t
on t.box_id = box.id
where t.box_id is null
order by box.id asc
;

問２

hasManyした子テーブルの「name」が「イチゴ」だけのもの（ただし、パイナップルはカウントから除外する）を取得する

boxそれぞれに「いちごであるbox_detailの合計件数」と「パイナップル以外のbox_detailの合計件数」をjoinして、いちごdetail数＝パイナップル以外detail数＞＝１　であるようなboxを選択する。

select
 box.name, box.id
 -- , strb.box_id, strb.cnt
 -- , all_but_pna.box_id, all_but_pna.cnt
from box
left outer join 
(select box_id, count(*) as cnt from box_detail where name = 'いちご' group by box_id) as strb
on strb.box_id = box.id
left outer join
(select box_id, count(*) as cnt  from box_detail where name is null or name <> 'パイナップル' group by box_id) as all_but_pna
on all_but_pna.box_id = box.id
where coalesce(strb.cnt, 0) >= 1 and coalesce(strb.cnt, 0) = coalesce(all_but_pna.cnt, 0)
order by box.id
;

問２　別実装

select
 box.name, box.id
 -- , details.strb_cnt, details.all_but_pna_cnt
from box
left outer join 
(select
    box_id
    , sum(case when name = 'いちご' then 1 else 0 end) as strb_cnt
    , sum(case when name is null or name <> 'パイナップル' then 1 else 0 end) as all_but_pna_cnt
    from box_detail group by box_id
) as details
on details.box_id = box.id
where coalesce(details.strb_cnt, 0) > 0 and coalesce(details.strb_cnt, 0) = coalesce(details.all_but_pna_cnt, 0)
order by box.id
;

Hope this helps.