結合したテーブルから複数条件に該当する列を取得するSQL

Question

前提

SQLServer2019にてSQL文を用いたプログラムを作成しております。
今回の質問に関連するテーブルとして以下の３つのテーブルがあります。

●生物テーブル
生物コード| 生物名
0001|ヒト
0002|カメ
0003|フラミンゴ
（以下生物名が続く）

●部位テーブル
部位コード| 部位名
1001|足
1002|手の指
1003|目
（以下部位名が続く）

●部位数テーブル
生物コード| 部位コード|部位数
0001|1001|2
0001|1002|10
0001|1003|2
0002|1001|4
0002|1002|0
0002|1003|2
0003|1001|1
0003|1002|0
0003|1003|2
（以下部位数が続く）

実現したいこと

部位と部位数を複数条件に設定したSQL文（条件文）を作成し、生物名(生物コード)を得たい。

上記の例から申しますと
「足の数が２本以上で目が２つ以上ある生物は？」というSQL文を作成して
「ヒトとカメ」という答えを得たいです。

試したこと

生物テーブルと部位数テーブルを結合するところまでは理解しております。

select 生物名 from 生物テーブル inner join 部位数テーブル on 生物テーブル.生物コード = 部位数テーブル.生物コード

この後どのように条件指定のSQL文を書けば良いのでしょうか？

Answer 1

「足の数が２本以上で目が２つ以上ある生物は？」という条件は「「足の数が２本以上」かつ「目が２つ以上」ある生物は？」という2つの事象を満たす時ですから、質問のテーブル構成だと問い合わせは2回必要です。

素直に記述するなら以下です。

select * from 生物
where 生物コード in (
        select 生物コード 
        from  部位数
        where 部位コード in (
                select 部位コード from  部位 where 部位名='足'
              )
          and 部位数>=2 
      )
  and 生物コード in (
        select 生物コード 
        from  部位数
        where 部位コード in (
                select 部位コード from  部位 where 部位名='目'
              )
          and 部位数>=2 
      )

ですが、UI操作の場合には部位選択時にコードを判明させる事が出来るので、部位テーブルを参照する必要が無く以下の様な問い合わせになるでしょう。

select * from 生物
where 生物コード in (
        select 生物コード 
        from  部位数
        where 部位コード = '1001'
          and 部位数>=2 
      )
  and 生物コード in (
        select 生物コード 
        from  部位数
        where 部位コード = '1003'
          and 部位数>=2 
       )

Comments

[sashimi1221]

ご回答ありがとうございます！
JOINしなくてもwhere文を駆使するだけで出来るんですね。
この方法で実装しようと思います。

Answer 2

以下のようなクエリで実現できると思います。

SELECT A.生物コード, A.生物名
FROM 生物テーブル A
JOIN 部位数テーブル B ON A.生物コード = B.生物コード
JOIN 部位テーブル C ON B.部位コード = C.部位コード
GROUP BY A.生物コード, A.生物名
HAVING MAX(CASE WHEN C.部位名 = N'足' THEN B.部位数 END) >= 2
AND MAX(CASE WHEN C.部位名 = N'目' THEN B.部位数 END) >= 2

Comments

[sashimi1221]

ご回答ありがとうございます！
この方法で実現できました！