汎用性があるSQL式を教えて欲しいです

質問

フラグ ナンバー 名前 カテゴリ 日付
1 大根 野菜 10/1
△ 2 りんご 果物 10/2
3 りんご 果物
4 牛肉 肉類 10/3

このような表を
フラグ ナンバー 名前 カテゴリ 日付
1 大根 野菜 10/1
3 りんご 果物 10/2
4 牛肉 肉類 10/3
このようにselectで表示させたいのですが
自分が考えたのは果物だけのテーブルを作り、表示させたいようにinner joinで果物のりんご同士を結合させ日付は△のを表示させるように作り
果物以外のテーブルとUnionさせたのですが
もう少し汎用性があるものへ昇華させたいとのですが考えが及びません
他にどのように表現出来るでしょうか？

回答No.5

piroin654

質問の「汎用性があるSQL式」というのは、質問の例では、
「△」がついているのはりんごだけですが、これがたとえば、

フラグ　ナンバー　　　　名前　　カテゴリ　　　　　　日付
△　　　　　1　　　　　　大根　　　野菜　　　　　2019/10/01
△　　　　　2　　　　　　りんご　　果物　　　　　2014/10/02
　　　　　　3　　　　　　りんご　　果物
　　　　　　4　　　　　　牛肉　　　肉類　　　　　2019/10/03
　　　　　　5　　　　　　大根　　　野菜
　　　　　　6　　　　　　みかん　　果物　　　　　2019/10/09

のようにデータを追加した場合にユニオンを使うと「△」がついた
数だけユニオン式を追加していかなくてはならないのでこれを避けたい
ということだと解釈して、テーブルの名前を「テーブル1」とすれば、

SELECT Temp1.フラグ, Temp1.ナンバー, Temp1.名前, Temp1.カテゴリ, IIf(Temp1.カテゴリ = (SELECT Temp3.カテゴリ FROM テーブル1 As Temp3 WHERE Temp3.フラグ = ('△') and Temp1.カテゴリ=Temp3.カテゴリ AND Temp1.名前=Temp3.名前 AND IsNull(Temp1.日付)),
(SELECT Temp2.日付
FROM テーブル1 As Temp2
WHERE Temp2.フラグ = '△' AND Temp1.カテゴリ=Temp2.カテゴリ AND Temp1.名前=Temp2.名前),Temp1.日付) AS 日付
FROM テーブル1 AS Temp1
WHERE (Temp1.フラグ) Is Null;

という選択クエリで対応できます。この選択クエリはIIf関数と二つの
サブクエリを内蔵しています。少々複雑ですが一応質問の意図と
見える範囲での条件で設定しています。
この場合クエリの結果は、

フラグ　ナンバー　　　　名前　　カテゴリ　　　　　　日付
　　　　　　3　　　　　　りんご　　果物　　　　　2014/10/02 　　　　　
　　　　　　4　　　　　　牛肉　　　肉類　　　　　2019/10/03
　　　　　　5　　　　　　大根　　　野菜　　　　　2019/10/01 　　
　　　　　　6　　　　　　みかん　　果物　　　　　2019/10/09

のようになります。
ただし、

フラグ　ナンバー　　　　名前　　カテゴリ　　　　　　日付
△　　　　　1　　　　　　大根　　　野菜　　　　　2019/10/01
△　　　　　2　　　　　　りんご　　果物　　　　　2014/10/02
　　　　　　3　　　　　　りんご　　果物
　　　　　　4　　　　　　牛肉　　　肉類　　　　　2019/10/03
　　　　　　5　　　　　　大根　　　野菜
　　　　　　6　　　　　　みかん　　果物　　　　　2019/10/09
　　　　　　7　　　　　　りんご　　果物

のようにナンバー7のりんごがある場合は、

フラグ　ナンバー　　　　名前　　カテゴリ　　　　　　日付
　　　　　　3　　　　　　りんご　　果物　　　　　2014/10/02 　　　　　
　　　　　　4　　　　　　牛肉　　　肉類　　　　　2019/10/03
　　　　　　5　　　　　　大根　　　野菜　　　　　2019/10/01 　　
　　　　　　6　　　　　　みかん　　果物　　　　　2019/10/09
　　　　　　7　　　　　　りんご　　果物　　　　　2014/10/02

のようにナンバー7のりんごの日付がナンバー2のりんごの日付が表示されますが、

フラグ　ナンバー　　　　名前　　カテゴリ　　　　　　日付
△　　　　　1　　　　　　大根　　　野菜　　　　　2019/10/01
△　　　　　2　　　　　　りんご　　果物　　　　　2014/10/02
　　　　　　3　　　　　　りんご　　果物
　　　　　　4　　　　　　牛肉　　　肉類　　　　　2019/10/03
　　　　　　5　　　　　　大根　　　野菜
　　　　　　6　　　　　　みかん　　果物　　　　　2019/10/09
△　　　　　7　　　　　　りんご　　果物　　　　　2019/10/10
　　　　　　8　　　　　　りんご　　果物

のようにナンバー7のりんごに日付があり、フラグが「△」で、
ナンバー8にりんごがあり日付が無い場合、上記のクエリを実行すると

フラグ　ナンバー　　　　名前　　カテゴリ　　　　　　日付
　　　　　　3　　　　　　りんご　　果物　　　　　2014/10/02
　　　　　　4　　　　　　牛肉　　　肉類　　　　　2019/10/03
　　　　　　5　　　　　　大根　　　野菜　　　　　2019/10/01
　　　　　　6　　　　　　みかん　　果物　　　　　2019/10/09
　　　　　　7　　　　　　りんご　　果物　　　　　2019/10/10
　　　　　　8　　　　　　りんご　　果物　　　　　2014/10/02

最低でも上記のようになればいいのですが、残念ながらこの場合は
エラーがでます。というのもサブクエリではレコードを一つしか
抽出できない決まりがあるからです。もしナンバー7にフラグ「△」
がなければ、上記の結果がでますが、ただし、この場合はナンバー7の
日付はどのりんごのデータに挿入するのか明らかになっていないし、
質問のデータの編集の意図がこれ以上に明らかでないので、このように
りんごのデータが重複してフラグに「△」がついているものとついて
いないものの組み合わせがないものとすれば上記のクエリで質問の
「汎用性のあるSQL式」の回答とします。

もし、データの編集に条件が加わるならば可能であれば回答します。
わからないところがあれば補足してください。