SQLで異なるテーブルそれぞれの条件を満たす行を取得したい

Question

環境

・SQLServer2008

やりたいこと

異なるテーブルそれぞれの条件を満たす行を一発のクエリで取得する事は可能でしょうか。
テーブル構造が結構複雑で文章だと説明が難しいため、詳細は下記を御覧ください。

商品マスタ関連

・商品には種別が存在しており、１商品に付き複数の種別を設定できる
・「商品種別紐付けマスタ」を元に「商品」と「種別」の紐付けを行っている

---

オプション関連

・商品種別は別に「オプション」が存在している
・オプションは商品マスタに紐づくのではなく、注文トランに紐づく
・１注文に付き複数オプションが選択できる

割引マスタ関連

・「商品種別」と「オプション」の組み合わせによって割引マスタが決定される
・商品種別のみで判断する場合もあれば、オプションのみで判断する場合もある
・条件に一致する割引マスタが取得されればOK（＝複数行が取得される場合もあるということ）

---

---

望まれる結果

---

CREATE文

USE [test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[m_option](
	[option_id] [int] NOT NULL,
	[option_name] [nchar](50) NULL,
 CONSTRAINT [PK_m_option] PRIMARY KEY CLUSTERED 
(
	[option_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[m_shohin](
	[shohin_id] [int] NOT NULL,
	[shohin_name] [nchar](50) NULL,
 CONSTRAINT [PK_m_shohin] PRIMARY KEY CLUSTERED 
(
	[shohin_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[m_shohin_shubetsu](
	[shohin_id] [int] NOT NULL,
	[shubetsu_id] [int] NOT NULL,
 CONSTRAINT [PK_m_shohin_syubetu] PRIMARY KEY CLUSTERED 
(
	[shohin_id] ASC,
	[shubetsu_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[m_shubetsu](
	[shubetsu_id] [int] NOT NULL,
	[shubetsu_name] [nchar](50) NULL,
 CONSTRAINT [PK_m_syubetu] PRIMARY KEY CLUSTERED 
(
	[shubetsu_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[m_waribiki](
	[waribiki_id] [int] NOT NULL,
	[waribiki_name] [nchar](50) NULL,
	[waribiki_ritsu] [int] NULL,
 CONSTRAINT [PK_m_waribiki] PRIMARY KEY CLUSTERED 
(
	[waribiki_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[m_waribiki_option](
	[waribiki_id] [int] NOT NULL,
	[option_id] [int] NOT NULL,
 CONSTRAINT [PK_m_warbiki_option] PRIMARY KEY CLUSTERED 
(
	[waribiki_id] ASC,
	[option_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[m_waribiki_shubetsu](
	[waribiki_id] [int] NOT NULL,
	[shubetsu_id] [int] NOT NULL,
 CONSTRAINT [PK_m_waribiki_syubetu] PRIMARY KEY CLUSTERED 
(
	[waribiki_id] ASC,
	[shubetsu_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t_uriage](
	[denp_no] [int] NOT NULL,
	[det_no] [int] NOT NULL,
	[shohin_id] [int] NULL,
	[su] [int] NULL,
 CONSTRAINT [PK_t_uriage] PRIMARY KEY CLUSTERED 
(
	[denp_no] ASC,
	[det_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t_uriage_option](
	[denp_no] [int] NOT NULL,
	[det_no] [int] NOT NULL,
	[option_id] [int] NOT NULL,
 CONSTRAINT [PK_t_uriage_option] PRIMARY KEY CLUSTERED 
(
	[denp_no] ASC,
	[det_no] ASC,
	[option_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[m_option] ([option_id], [option_name]) VALUES (1, N'3営業日以降出荷                                          ')
GO
INSERT [dbo].[m_option] ([option_id], [option_name]) VALUES (2, N'包装紙無し                                             ')
GO
INSERT [dbo].[m_shohin] ([shohin_id], [shohin_name]) VALUES (1, N'明治チョコレート                                          ')
GO
INSERT [dbo].[m_shohin] ([shohin_id], [shohin_name]) VALUES (2, N'グリコチョコレート                                         ')
GO
INSERT [dbo].[m_shohin] ([shohin_id], [shohin_name]) VALUES (3, N'グリコクッキー                                           ')
GO
INSERT [dbo].[m_shohin] ([shohin_id], [shohin_name]) VALUES (4, N'明治チョコ&クッキーセット                                     ')
GO
INSERT [dbo].[m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (1, 1)
GO
INSERT [dbo].[m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (1, 3)
GO
INSERT [dbo].[m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (2, 2)
GO
INSERT [dbo].[m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (2, 3)
GO
INSERT [dbo].[m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (3, 2)
GO
INSERT [dbo].[m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (3, 4)
GO
INSERT [dbo].[m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (4, 1)
GO
INSERT [dbo].[m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (4, 3)
GO
INSERT [dbo].[m_shohin_shubetsu] ([shohin_id], [shubetsu_id]) VALUES (4, 4)
GO
INSERT [dbo].[m_shubetsu] ([shubetsu_id], [shubetsu_name]) VALUES (1, N'明治                                                ')
GO
INSERT [dbo].[m_shubetsu] ([shubetsu_id], [shubetsu_name]) VALUES (2, N'グリコ                                               ')
GO
INSERT [dbo].[m_shubetsu] ([shubetsu_id], [shubetsu_name]) VALUES (3, N'チョコレート                                            ')
GO
INSERT [dbo].[m_shubetsu] ([shubetsu_id], [shubetsu_name]) VALUES (4, N'クッキー                                              ')
GO
INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritsu]) VALUES (1, N'チョコレート割                                           ', 10)
GO
INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritsu]) VALUES (2, N'包装紙無し割引                                           ', 5)
GO
INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritsu]) VALUES (3, N'3営業日以降出荷 割引(明治チョコ限定)                              ', 3)
GO
INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritsu]) VALUES (4, N'3営業日以降出荷 割引+包装紙無し割引(チョコレート限定)                     ', 5)
GO
INSERT [dbo].[m_waribiki] ([waribiki_id], [waribiki_name], [waribiki_ritsu]) VALUES (5, N'3営業日以降出荷 割引+包装紙無し割引(グリコクッキー限定)	                   ', 5)
GO
INSERT [dbo].[m_waribiki_option] ([waribiki_id], [option_id]) VALUES (2, 2)
GO
INSERT [dbo].[m_waribiki_option] ([waribiki_id], [option_id]) VALUES (3, 1)
GO
INSERT [dbo].[m_waribiki_option] ([waribiki_id], [option_id]) VALUES (4, 1)
GO
INSERT [dbo].[m_waribiki_option] ([waribiki_id], [option_id]) VALUES (4, 2)
GO
INSERT [dbo].[m_waribiki_option] ([waribiki_id], [option_id]) VALUES (5, 1)
GO
INSERT [dbo].[m_waribiki_option] ([waribiki_id], [option_id]) VALUES (5, 2)
GO
INSERT [dbo].[m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (1, 3)
GO
INSERT [dbo].[m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (3, 1)
GO
INSERT [dbo].[m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (3, 3)
GO
INSERT [dbo].[m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (4, 3)
GO
INSERT [dbo].[m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (5, 2)
GO
INSERT [dbo].[m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (5, 3)
GO
INSERT [dbo].[t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (1, 1, 1, 5)
GO
INSERT [dbo].[t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (2, 1, 3, 5)
GO
INSERT [dbo].[t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (3, 1, 1, 5)
GO
INSERT [dbo].[t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (4, 1, 3, 5)
GO
INSERT [dbo].[t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (5, 1, 3, 5)
GO
INSERT [dbo].[t_uriage] ([denp_no], [det_no], [shohin_id], [su]) VALUES (5, 2, 1, 10)
GO
INSERT [dbo].[t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (2, 1, 2)
GO
INSERT [dbo].[t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (3, 1, 2)
GO
INSERT [dbo].[t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (4, 1, 1)
GO
INSERT [dbo].[t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (4, 1, 2)
GO
INSERT [dbo].[t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (5, 1, 1)
GO
INSERT [dbo].[t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (5, 1, 2)
GO
INSERT [dbo].[t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (5, 2, 1)
GO
INSERT [dbo].[t_uriage_option] ([denp_no], [det_no], [option_id]) VALUES (5, 2, 2)
GO

Comments

[Orlofsky]

現行の途中までできているであろうSELECT文も載せましょう。ここの掲示板は丸投げは非推奨です。

[Orlofsky]

ローマ字表はヘボン式を使われては？ヘボン式を使わないで損をする人が多いです。
https://www.ezairyu.mofa.go.jp/passport/hebon.html

[smaeda]

下記SQLのように、売上伝票 配送オプション管理テーブルから、
まずは配送オプション割引条件マスタを絞り込み、
更にそこから商品種別割引条件マスタを絞り込もうと思ったのですが、
下記方法だと、オプションが選択されていない注文の場合に取得出来ないことに気がつき、
行き詰まってしまいました・・・
（文字数制限に引っかかり質問の欄に追記ができないため、こちらにSQLを記載いたします）

またヘボン式の件に関しましても、ご教示いただきありがとうございます。

select
*
from
(
select
t_uriage_option.option_id,
m_waribiki_syubetu.waribiki_id,
count(*) need_hantei,
sum(case when m_shohin_syubetu.syubetu_id is not null then 1 else 0 end) jissai_hantei
from
t_uriage
left join
t_uriage_option
on t_uriage.denp_no = t_uriage_option.denp_no
and t_uriage.det_no = t_uriage_option.det_no
left join
m_waribiki_option
on t_uriage_option.option_id = m_waribiki_option.option_id
left join
m_waribiki_syubetu
on m_waribiki_option.waribiki_id = m_waribiki_syubetu.waribiki_id
left join
m_shohin_syubetu
on t_uriage.shohin_id = m_shohin_syubetu.shohin_id
and m_waribiki_syubetu.syubetu_id = m_shohin_syubetu.syubetu_id
where
t_uriage.denp_no = 1
group by
t_uriage_option.option_id,
m_waribiki_syubetu.waribiki_id
) tmp
where
need_hantei = jissai_hantei

[Orlofsky]

ここではなく質問を修正してください。ここはMarkdownを使えません。
syubetu
↓
shubetsu

[smaeda]

申し訳ありません。テーブル名やカラム名はヘボン式に修正しました。

実際に試してみたSQLに関しては、文字数の制限で質問欄にこれ以上追記が出来ないため
こちらに記載させてください。

select
*
from
(
select
t_uriage_option.option_id,
m_waribiki_shubetsu.waribiki_id,
count(*) need_hantei,
sum(case when m_shohin_shubetsu.shubetsu_id is not null then 1 else 0 end) jissai_hantei
from
t_uriage
left join
t_uriage_option
on t_uriage.denp_no = t_uriage_option.denp_no
and t_uriage.det_no = t_uriage_option.det_no
left join
m_waribiki_option
on t_uriage_option.option_id = m_waribiki_option.option_id
left join
m_waribiki_shubetsu
on m_waribiki_option.waribiki_id = m_waribiki_shubetsu.waribiki_id
left join
m_shohin_shubetsu
on t_uriage.shohin_id = m_shohin_shubetsu.shohin_id
and m_waribiki_shubetsu.shubetsu_id = m_shohin_shubetsu.shubetsu_id
where
t_uriage.denp_no = 1
group by
t_uriage_option.option_id,
m_waribiki_shubetsu.waribiki_id
) tmp
where
need_hantei = jissai_hantei

[Orlofsky]

SELECT文も質問に追加してください。

[smaeda]

申し訳ありませんが、文字数の制限に引っかかり
質問欄にこれ以上の追記が出来ない状況です・・・

[Orlofsky]

ちらっと見ただけですが、

テーブル定義で m_shohin_shubetsu の shubetsu_id not null
NOT NULL制約があるからNULLのデータはINSERTできないのに
SELECT文で
sum(case when m_shohin_shubetsu.shubetsu_id is not null then 1 else 0 end) jissai_hantei
や
need_hantei = jissai_hantei
は意味があるのですか？

[smaeda]

①t_uriage_optionとm_waribiki_optionをJOINする事で条件に一致する「m_waribiki.waribiki_id」を特定
②「①」で特定したwairibiki_idを元にm_waribiki_shubetsuをJOIN
③更にm_shohin_shubetsuをJOINする

「③」の時点で、割引対象となる商品種別と商品マスタで設定されている種別が一致しない行は「m_shohin_shubetsu.shubetsu_id」がNULLとなるので
NULL判定を行う事によって双方の種別が合致しているかの判断が出来るかなと考えました。

ですが、そもそもオプションが選択されている事が大前提なクエリだと言うことに気が付き
行き詰まってしまった状況となります・・・

[Orlofsky]

SQLって最初から目標レベルで記述するのではなく、正常に動作することを確認しながらJOINを1つずつ追加して行くものです。その途中でテーブル設計がこれではまずい、とか気がつける場合もあります。
また、通常
SEECT T1.SHUBETSU FROM TABLE1 T1 ;
のようにテーブルの別名を使うことでSQLをちょっとシンプルにできます。

[Orlofsky]

ここまではうまくいったというSELECT文とその結果、その次はエラーになったとか希望とは違うというSELECT文とその結果をこの下に追加しては？

[sazi]

@smaedaさん
ここへの追記は、質問の続きとして回答の方に書かれた方が良いですね。

[sazi]

パターン⑤だけ複数の明細が条件になっているんですけど、パターン⑤以外は明細が1行のみという条件が暗黙ですか？

Answer 1

テーブルの定義、サンプルデータ、期待する結果から、以下のSQLで目的の結果が得られると思いますが、いかがでしょうか？
もっと簡潔に書けそうな気もしたのですが、これが限界でした。
0. 売上に売上オプションと売上商品種別を結合 → A
0. 割引に割引オプションと割引商品種別を結合 → B
0. AとBを結合して割引の対象とみなせる売上オプションと売上商品種別に絞り込む
0. 伝票、明細に対して各割引で有効だったオプションと種別の数を取得 → uu
0. 割引に割引オプションと割引商品種別を結合しオプションと種別の数を取得 → ww
0. uuとwwを以下の条件で結合
a. 割引IDが一致している
b. ww側のオプションが0(オプションは無視)か、uuとオプションの個数が一致するか大きい(全部のオプションが売上側に存在)
c. ww側の商品種別が0(商品種別は無視)か、uuと商品種別の個数が一致するか大きい(全部の商品種別が売上側に存在)

SELECT DISTINCT
    uu.denp_no,
    ww.waribiki_id,
    ww.waribiki_name,
    ww.waribiki_ritsu
FROM
(
SELECT
    u.denp_no,
    u.det_no,
    w.waribiki_id,
    COUNT(DISTINCT uo.option_id) AS option_num,
    COUNT(DISTINCT us.shubetsu_id) AS shubetsu_num
FROM
    t_uriage u
    LEFT OUTER JOIN t_uriage_option uo
        ON u.denp_no=uo.denp_no AND u.det_no=uo.det_no
    INNER JOIN m_shohin_shubetsu us
        ON u.shohin_id=us.shohin_id
    LEFT OUTER JOIN m_waribiki w
        LEFT OUTER JOIN m_waribiki_option wo
            ON w.waribiki_id=wo.waribiki_id
        LEFT OUTER JOIN m_waribiki_shubetsu ws 
            ON w.waribiki_id=ws.waribiki_id
    ON wo.option_id IS NULL AND us.shubetsu_id=ws.shubetsu_id OR
       uo.option_id=wo.option_id AND ws.shubetsu_id IS NULL OR
       uo.option_id=wo.option_id AND us.shubetsu_id=ws.shubetsu_id
GROUP BY
    u.denp_no,
    u.det_no,
    w.waribiki_id
) uu
INNER JOIN
(
SELECT
    w.waribiki_id,
    MAX(w.waribiki_name) AS waribiki_name,
    MAX(w.waribiki_ritsu) AS waribiki_ritsu,
    COUNT(DISTINCT wo.option_id) AS option_num,
    COUNT(DISTINCT ws.shubetsu_id) AS shubetsu_num
FROM
    m_waribiki w
    LEFT OUTER JOIN m_waribiki_option wo
        ON w.waribiki_id=wo.waribiki_id
    LEFT OUTER JOIN m_waribiki_shubetsu ws 
        ON w.waribiki_id=ws.waribiki_id
GROUP BY
    w.waribiki_id
) ww
ON uu.waribiki_id=ww.waribiki_id AND
   (ww.option_num=0 OR
    uu.option_num>=ww.option_num) AND
   (ww.shubetsu_num=0 OR
    uu.shubetsu_num>=ww.shubetsu_num)
ORDER BY 1,2
;

テーブル定義の情報を質問に書くと長くなってしまうので、dbfiddleなどを使って定義を説明するとよいかもしれません。
上記のSQLの実行結果は以下のリンクを参照してください。（一番最後のSQLです）
dbfiddle

あと、サンプルデータに誤りがあると思いますので、修正したものを使用しています。

誤

INSERT [dbo].[m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (5, 2)
GO
INSERT [dbo].[m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (5, 3)
GO

↓ グリコチョコではなくグリコクッキーなので
正

INSERT [m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (5, 2)
GO
INSERT [m_waribiki_shubetsu] ([waribiki_id], [shubetsu_id]) VALUES (5, 4)
GO