SQLでINNER JOINで結合する際の条件にORが必要なケースで取得できません。

Question

下記のようなテーブルから、下記の条件に該当するデータをINNER JOINを使って結合して取得したいです。

【前提】
現在日付を2022/12/07とする。

・テーブルA の post_status が publishのもの
・テーブルB の meta_keyに延長期限が設定されていれば、その値が現在日時以前のもの
・テーブルB の meta_keyに延長期限が設定されていないか、または空白の場合は、meta_key=有効期限の値が現在日時以前のもの

サンプルデータでいうと、post_idの1,2,3が取得される想定です。

上記に当てはまる条件式でSQLを書こうとしたのですが、JOINのON句の中で条件式を書こうとすると、meta_valueの指定でおかしくなっているようで取得できません。
何か良い方法はないでしょうか。

SELECT
	post.ID
FROM
	wp_posts AS post
	INNER JOIN wp_postmeta AS pm ON
		post.ID = pm.post_id AND
		(pm.meta_key = '延長期限' AND pm.meta_value IS NOT NULL AND pm.meta_value >= 2022-12-07) OR
		(pm.meta_key = '延長期限' AND pm.meta_value IS NULL AND pm.meta_key = '有効期限' AND pm.meta_value >= 2022-12-07)
	WHERE
		(post.post_status = 'publish')
	ORDER BY
		pm.meta_value

【追記】
サンプルデータのcreate文を記載します。

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `wp_postmeta`;
CREATE TABLE `wp_postmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) DEFAULT NULL,
  `meta_value` longtext,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `wp_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES
(1,	1,	'有効期限',	'2000-01-01'),
(2,	1,	'延長期限',	'2023-01-01'),
(3,	2,	'有効期限',	'2023-01-01'),
(4,	3,	'有効期限',	'2023-01-01'),
(5,	3,	'延長期限',	''),
(6,	4,	'有効期限',	'2000-01-01');

DROP TABLE IF EXISTS `wp_posts`;
CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_status` varchar(20) NOT NULL DEFAULT 'publish',
  PRIMARY KEY (`ID`),
  KEY `type_status_date` (`post_status`,`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Comments

[yambejp]

サンプルデータをcreate table+insert形式で例示ください
それと想定する結果も追記ください

[Anon_]

追記に提示しました。

[Anon_]

サンプルデータでいうと、post_idの1,2,3が取得される想定です。
本文にも追記しました。

Answer 1

(pm.meta_key = '延長期限' AND pm.meta_value IS NULL AND pm.meta_key = '有効期限' AND pm.meta_value >= 2022-12-07)

pm.meta_key = '延長期限'かつpm.meta_key = '有効期限'という事態は発生しません。

Comments

[Anon_]

そうですよね。
ただ、SQLにはIF関数がないのでどう分岐すると良いのかが分かりません。
INNNER JOINのON句で絞るのではなく、
一度取り出した値をwhereで絞ると良いのでしょうか？

[maisumakun]

（誤解のため取り消し）

[Anon_]

では矛盾のない条件式を書くにはどうしたらよいですか？
やりたいことは明記しているので、その方法が分からず ON句の中でやろうとするとこうなってしまうという内容の質問なので、私が試したコードがおかしいのは私自身わかっています。

[maisumakun]

wp_postの1行に対してwp_postmetaの複数行を見ないといけない条件である以上は、wp_posts INNER JOIN wp_postmetaに対するONの形（両者を1項目ずつ組み合わせてできた行に対して条件を絞り込む）では書けません。

[maisumakun]

有効期限より延長期限のほうがあとに来るのであれば、SELECT post_id, MAX(meta_value) GROUP BY post_idを行って、その結果をwp_postにJOINする、という方法が考えられます。

[Anon_]

なるほどMAX関数を使う手がありますか。
上記の場合、サブクエリをFROMの後ろに仕込む形でしょうか。

[Anon_]

テーブルやデータは違いますがこんな感じでしょうか。

SELECT h.* FROM ( SELECT * FROM hoge WHERE name = 'a') AS h
INNER JOIN foo AS f ON h.id = f.id
WHERE f.name = 'b';

[Anon_]

延長期限と有効期限が重複する箇所はleft joinに変更して、where句内でORを使用して取得できたように思います。
create tableを追記しましたので、よろしければMAX関数を使用した取得例を示していただけると参考になります。

Answer 2

とりあえずこんな感じにして、あとは条件を追加すればよいでしょう

select * from wp_postmeta as t1
left join  wp_postmeta as t2
on t1.post_id=t2.post_id
and t2.meta_key='延長期限'
where t1.meta_key='有効期限'

追記

有効期限化延長期限が本日よりあとで、statusがpublishなデータ

select * from wp_postmeta as t1
inner join wp_posts as t2
on t1.post_id=t2.id and t2.post_status='publish'
left join  wp_postmeta as t3
on t1.post_id=t3.post_id
and t3.meta_key='延長期限'
where t1.meta_key='有効期限'
and (t1.meta_value>curdate() or t3.meta_value>curdate())

上記からidだけをとりだすなら

select t1.post_id from wp_postmeta as t1
inner join wp_posts as t2
on t1.post_id=t2.id and t2.post_status='publish'
left join  wp_postmeta as t3
on t1.post_id=t3.post_id
and t3.meta_key='延長期限'
where t1.meta_key='有効期限'
and (t1.meta_value>curdate() or t3.meta_value>curdate())

Comments

[Anon_]

条件はどこに追加しますか？

[yambejp]

サンプルデータをきちんと提示いただければ回答します

[Anon_]

追記に提示しました

[Anon_]

延長期限と有効期限が重複する箇所はleft joinに変更して、where句内でORを使用して取得できたように思います。

[yambejp]

調整しておきました。
有効期限と延長期限のどちらかが今日よりあとだったらOKでしょう

[Anon_]

例文ありがとうございます。
curdate()など知らない関数を知れて勉強になりました。

ただ、例文のクエリですと延長期限の値が有効期限以前の値が登録されている場合、下記の条件を満たさなくなりますが、取得されてしまいますので、若干where句の修正が必要になりそうです。

・テーブルB の meta_keyに延長期限が設定されていれば、その値が現在日時以前のもの

あとは自分でなんとかできそうです。
ありがとうございました。

他の方の回答がなければBAとさせていただこうと思います。

[yambejp]

あれ？当日が有効期限か延長期限より前であればよいような気がしますが違うのでしょうか・・・

[Anon_]

通常は仰る通りかと思います。
引き継いだシステムが商品に対して一度登録した有効期限は変更できないという制約があることから延長期限を設定したいという要望を受けました。
万一、有効期限を短縮したいというケースが発生した際に延長期限を利用する可能性がありますので、念のため有効期限より前の延長期限が設定されるケースにも一応対応しておきたいという事です。
延長期限というよりは優先度の高い第二の有効期限と説明した方が理解しやすいかもしれません。
説明が不足しており申し訳ありません。
大変助かりました。

[yambejp]

> 延長期限というよりは優先度の高い第二の有効期限

ということなら以下でよいでしょう
select *
from wp_postmeta as t1
inner join wp_post as t2
on t1.post_id=t2.id and t2.post_status='publish'
left join wp_postmeta as t3
on t1.post_id=t3.post_id
and t3.meta_key='延長期限'
where t1.meta_key='有効期限'
and coalesce(t3.meta_value,t1.meta_value)>CURDATE()

[Anon_]

最後までありがとうございます。
coalesceという関数は色々使いどころがありそうですね。
しかし、延長期限には空白が登録されるケースがありますので、
この場合、coalesceにより有効期限を上書きしてしまいますので、
やはりwhere内で細かく条件を指定するしかないですかね。

Answer 3

ぱっと見、

  (pm.meta_key = '延長期限' AND pm.meta_value IS NULL AND pm.meta_key = '有効期限' AND pm.meta_value >= 2022 ...
    ↓
  (pm.meta_key = '延長期限' AND
   pm.meta_key = '有効期限' AND -- pm.meta_key が 延長期限 かつ 有効期限 ってあり得ないし、
   pm.meta_value IS NULL AND 
   pm.meta_value >= '2022-12-07')  -- pm.meta_value IS NULL AND pm.meta_value >= '2022-12-07' もあり得ないのでは？

質問にCREATE TABLE文を提示できるようになってください。

Comments

[Anon_]

そうですね。
create分があれば確認していただくのがスムーズになりますね！
今後は気を付けます。

SQL分については、やりたい条件は明記しています。

[Anon_]

追記に提示しました。

[Orlofsky]

>今後は気を付けます。

分と文の違いはわかってね。
質問は修正できます。

CREATE TABLE文は今回も載せた方が良いかと。

ちゃんと回答を読みましたか？
>-- pm.meta_key が 延長期限 かつ 有効期限 ってあり得ないし、...

[Anon_]

追記に提示しました。

[Anon_]

延長期限と有効期限が重複する箇所はleft joinに変更して、where句内でORを使用して取得できたように思います。

[Orlofsky]

質問にサンプルデータをINSERT文で、以下のSELECT文と実行結果も提示してください。

>延長期限と有効期限が重複する箇所はleft joinに変更して、where句内でORを使用して取得できたように思います。