SQL SELECT時、他テーブルを参照してフラグを立てる

Question

SQL初心者ですが、お知恵を拝借させていただきたいです。

以下内容でemployee テーブルから項目をselectしたいのですが、まず結合が必要かどうかすらわからず苦戦しています。。。
添付画像も参照していただき、教えていただけないでしょうか。初歩的な質問で申し訳ありません。。汗

■したいこと
employee テーブルをselectする際、employee_idが同一のcustomer テーブルのcust_nameが
customer_2 テーブルに一つでも存在し、かつそのレコードのemployee_idが同一でない場合、
select項目の「flg」を１にし、どれも存在しない場合、または存在していてもemployee_idが同一の場合は「flg」を０にする。

以上、ご回答をお待ちしております。

■ 追記
説明にはcustomer テーブルとcustomer_2 テーブルのcust_nameが完全一致のものというような書き方をしましたが、
実際は部分一致したものにフラグを立てたいです。

sazi　様から頂いた回答の

on cst1.cust_name = cst2.cust_name
の部分を
on cst1.cust_name like CONCAT('%', cst2.cust_name, '%')

に変更して実行しましたが、やはり思うような結果が得られません。。
何か良い方法はありませんでしょうか。。？

■ サンプルデータ

customer.cst_name
hello株

customer_2.cst_name
hello株式会社

↑のようなデータ同士でも、部分一致で存在する判定にしたいです。

Comments

[Orlofsky]

テーブル定義やテーブル中のデータは CREATE TABLEとINSERT に https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正して、キャプチャは削ってください。

[Rena112]

キャプチャは添付しないほうがよろしいのでしょうか？
分からないことばかりで申し訳ありません。

Answer 1

SELECT E.employee_id, MAX(E.emp_name) AS emp_name
  , MAX(CASE WHEN C2.customer_id IS NULL THEN 0 ELSE 1) AS flg
FROM employee E
  LEFT JOIN customer C
    ON C.employee_id=E.employee_id
  LEFT JOIN customer_2 C2
    ON C2.cust_name=C.cust_name
      AND C2.employee!=C.employee_id
GROUP BY E.employee_id

って感じでしょうか。

Comments

[Rena112]

ご回答ありがとうございます。ご参考にさせていただきます。

Answer 2

・customer テーブルのcust_nameがcustomer_2 テーブルに一つでも存在し、かつそのレコードのemployee_idが同一でない場合
・どれも存在しない場合、または存在していてもemployee_idが同一の場合

上記は背反の関係ですから、一方の条件に当てはまるかどうかを判断すれば良い事になります。

求めるSELECTはemployeeの行数のままですから、flgの判定は相関サブクエリーとします。

select  employee.*
      , case when
          exists(
            select 1 
            from  customer cst1 
                  inner join customer_2 cst2
                  on    cst1.cust_name = cst2.cust_name 
                    and cst1.employee_id != cst2.employee_id
            where cst1.employee_id= employee.employee_id
          )
        then 1 else 0 end as flg
frpm employee

Comments

[Rena112]

ご回答ありがとうございます。内部結合をつかうのですね、、、なるほど、非常に勉強になりました。
ベストアンサーにさせていただきます。

[Rena112]

すみません。説明にはcust_nameが完全一致のものというような書き方をしましたが、実際は部分一致したものにフラグを立てたいです。。

on cst1.cust_name = cst2.cust_name
の部分を
on cst1.cust_name like CONCAT('%', cst2.cust_name, '%')

に変更して実行しましたが、やはり思うような結果が得られません。。
何か良い方法はありませんでしょうか。。？

[sazi]

MySQLですか？
どのようなデータ同士で比較しているか、サンプルデータを質問に追記して下さい。

[Rena112]

MYSQLです。承知しました。

[Rena112]

追記いたしました。ご確認お願い致します。

[sazi]

比較が逆です。
on cst2.cust_name like CONCAT('%', cst1.cust_name, '%')

[Rena112]

sazi さん

ありがとうございます。期待した通りのデータを取得することができました。

しかし恥ずかしながら、EXISTS内サブクエリの表のイメージが未だできていません。
もし可能でしたら簡単に説明いただきたいのですが、よろしいでしょうか。

[sazi]

結合のイメージはgpsoftさんの回答と同じです。
それとの違いは、集計(group by)をしていない点です。
Existsは１件でも該当するデータがあれば良いので、集計を必要としませんから。
あとは相関により、where 条件が結合の代わりとなっている点ですね。

[Rena112]

sazi　さん

ご回答ありがとうございます。非常に勉強になりました。