SQL片方から削除する

Question

前提

sqlのクエリを考えているのですが、目的の動きをするクエリを作れなかったので知恵を貸していただきたいです。

テーブルA

会社名	image	image2
companyA	0000.jpg	0001.jpg
companyA	0002.jpg	0003.jpg
companyB	1111.jpg	2222.jpg

テーブルB

key
image/hoge/companyA/0000/0000.jpg
image/hoge/companyA/0001/0001.jpg
image/hoge/companyB/1111/1111.jpg

テーブルBは
images/hoge/会社名/ファイル名(拡張子なし)/ファイル名となっています

期待する結果

テーブルAからテーブルBにある画像を消したいです。
テーブルAにしかない画像だけを残したい

試したこと

INNER JOINをYoutubeを見ながら試しましたが解決できませんでした。

Comments

[maisumakun]

imageとimage2の違いは何でしょうか？

[asukara]

写真が違います。
１つのレコードで1つの物件情報だとおもっていただけると
１つの物件に対して複数の写真があるイメージです。
３つ目以降は省略しました。
１つの会社が複数の物件を所有しています。

[javahack]

テーブルBの数字はどのような規則になっていますか？

[asukara]

修正しました。
拡張子なしのファイル名です

Answer 1

SUBSTRING_INDEXを使って文字列分割する以下の様なイメージになります。

select *
from ta
where not exists(
        select 1 from tb
        where SUBSTRING_INDEX(SUBSTRING_INDEX(key, '/', 3), '/', -1)=ta.会社名
          and SUBSTRING_INDEX(key, '/', 5) in (ta.image, ta.image2)
      )