mySQLで2つのテーブルのあるカラムが一致したレコードを抽出したいのですが、うまくいきません。

Question

前提・実現したいこと

mySQLで2つのテーブルのあるカラムが一致したレコードを抽出したいのですが、うまくいきません。

テーブルAには主キーのvidに複数のkeyとvalueがあり、 テーブルBにはcidに複数のkeyとvalueがあります。（正規化がうまくできていませんが、この構造を前提にプログラムの質問をさせてください。）
テーブルBのkeyとvalueに合致するA.vidを抽出することが目的です。
下記のテーブルの状態であれば、vid=1002が抽出できればよいのですが、うまくできていません。
初心者につき、ご教授いただければ幸いです。よろしくお願いいたします。

[テーブルA]
vid key value
1001 1 aaa
1001 2 bbb
1001 3 ccc
1002 1 ddd
1002 2 eee
1002 3 fff
1003 1 ggg
1003 2 hhh
1003 3 iii
;

[テーブルB]
cid key value
2001 1 ddd
2001 2 eee
2001 3 fff
;

該当のソースコード

SELECT vid FROM A WHERE
((value=(SELECT value FROM B WHERE key=1)) AND key=1)) AND
((value=(SELECT value FROM B WHERE key=2)) AND key=2)) AND
((value=(SELECT value FROM B WHERE key=3)) AND key=3));

試したこと

WHERE節のAND条件をバラして個別に実行すると、vid=1002が出力されますので、AND条件の考え方が誤解しているように思います。

SELECT vid FROM A WHERE
((value=(SELECT value FROM B WHERE key=1)) AND key=1));

SELECT vid FROM A WHERE
((value=(SELECT value FROM B WHERE key=2)) AND key=2));

SELECT vid FROM A WHERE
((value=(SELECT value FROM B WHERE key=3)) AND key=3));

よろしくお願いいたします。

Comments

[Orlofsky]

コードは https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正してください。
他のMarkdownの機能も確認しておいてください。

[Orlofsky]

教授 → 教示

Answer 1

縦を横に変換して結合してみる。
変換部分は**group_concat()**で変換できるような内容ならもう少し簡潔になるかもです。
[MySQL]GROUP_CONCATで縦のデータをカンマ区切りで取得

select vid
from (
        select vid
             , max(key1_value) as key1_value
             , max(key2_value) as key2_value
             , max(key3_value) as key3_value
        from (
          select  vid
                , case when key=1 then value end as Key1_value
                , case when key=2 then value end as Key2_value
                , case when key=3 then value end as Key3_value
          from A
        ) a_closs
        group by vid
    ) a_value inner join (
        select max(key1_value) as key1_value
             , max(key2_value) as key2_value
             , max(key3_value) as key3_value
        from (
          select case when key=1 then value end as Key1_value
               , case when key=2 then value end as Key2_value
               , case when key=3 then value end as Key3_value
          from B
        ) b_closs
    ) b_value
    on  a_value.key1_value=b_value.key1_value 
    and a_value.key2_value=b_value.key2_value
    and a_value.key3_value=b_value.key3_value

Comments

[sazi]

Bが１セットならBの整形はmaxでのグルーピングを使用せずにサブクエリーの方が高速な気がします。

[KojiUchida]

sazi様
ご確認ありがとうございます。
なるほど！！
横に変換することは考えていませんでしたが、その方法もありですね。
maxでもサブクエリでも実現できましたので、ベストアンサーとさせていただきます。ありがとうございました。

Answer 2

SELECT A.vid FROM A
INNER JOIN B
ON A.key = B.key ;

SQL入門レベルの勉強はきちんとやらないと仕事になりませんよ。

Comments

[KojiUchida]

ご回答ありがとうございます。正確には、
SELECT A.vid FROM A
INNER JOIN B
ON A.key = B.key AND A.value = B.value ;
ですね。
そもそも、質問の事例が適切ではなく、これでは聞きたいことが伝わらないので、本件はCloseとし、別で質問とさせていただきます。
※valueは数値で、key=1は等しい、key=2は以上、key=3は以下としたく、その場合、個別でWHERE節でANDを取らざるとえないとしていたため、JOINは避けていました。質問内容が適切でなく、申し訳ありませんでした。

[Orlofsky]

「質問への追記・修正」に修正依頼を書きましたが、
ここの掲示板は質問も回答も修正できるのはご存知で？