sql 片方にしか存在しないデータ抽出したい

Question

前提

2つのテーブルのうち片方にしか存在しないデータを抽出したい
自身でも考えましたが、一向に進まないのでお力添えをお願いします。

テーブル

テーブルA

company	image_file1	image_file2	image_file3
竹中不動産	000.jpg	301.jpg	002.jpg
竹中不動産	050.jpg	501.jpg	032.jpg
三葉不動産	110.jpg	161.jpg	112.jpg
ベニエル不動産	220.jpg	221.jpg	222.jpg

テーブルB

company	file_name
竹中不動産	000.jpg
竹中不動産	301.jpg
竹中不動産	002.jpg
竹中不動産	050.jpg
三葉不動産	110.jpg
三葉不動産	112.jpg
ベニエル不動産	220.jpg

期待する結果

company	image_file1	image_file2	image_file3
竹中不動産	501.jpg	032.jpg
三葉不動産	161.jpg
ベニエル不動産	221.jpg	222.jpg

テーブルAは各レコードが１つの物件情報
1つめの竹中不動産の場合
竹中不動産が所有する物件で画像が3枚あることを表す

自分で考えたこと

SELECT campany, image_file1, image_file2, image_file3
FROM tableA
LEFT OUTER JOIN table b
ON
までは考えたのですが、この後が続きません
そもそも上記の途中までのクエリ自体まちがっているかも・・・

Comments

[sazi]

タグが一杯付いていますが、使用するDBMSは何ですか？
それと、解決した前の質問はいったい何だったのでしょう？

[asukara]

使用するのはBigQuery上でですが、DBによって書き方変わるのでしょうか？
前の質問時点ではテーブルの内容が違います。

[javahack]

前の質問と類似の内容に思われます。
https://teratail.com/questions/ammllg4fq3s41h
応用することを考えましょう。

[m.ts10806]

「前の質問」確認しましたが、少し応用すればできると思います（ちゃんと理解したうえでの解決を心掛けてもらえると）

[asukara]

SELECT *
FROM ta
WHERE not exitsts(
select 1 from tb
where tb.company = ta.company
and tb.file_name in (ta.file_name1, ta.file_name2, ta.file_name3)
)

自分でもう一度考えると上記かなと思うのですが、1は何を表しているのでしょうか？
tbを上から1つづつ検証するための1ですか？

[m.ts10806]

1は単なる「１」という数値型の値です。固定値。
SELECT句には列が1つ以上必要なのでダミーで置いてるのでしょう。

Answer 1

使用するのはBigQuery上でですが、DBによって書き方変わるのでしょうか？

DBMSによりますが、変わります。BigQueryは標準SQLで実装されていないものもありますし。

前の質問時点ではテーブルの内容が違います。

前のはMYSQLの関数を使用していましたので、BigQueryでは解決するはずないんですけど。

今回のは、file_name1～3のカラム毎に表示/非表示を切り替えるのが目的なので、カラム毎の問い合わせが必要です。
なのでそれぞれのカラムに対して結合します。

SELECT ta.company
     , case when tb1.file_name is null then ta.file_name1 end as file_name1
     , case when tb2.file_name is null then ta.file_name2 end as file_name2
     , case when tb3.file_name is null then ta.file_name3 end as file_name3
FROM  ta
      left join tb tb1
      on    tb1.company = ta.company
        and tb1.file_name =ta.file_name1
      left join tb tb2
      on    tb2.company = ta.company
        and tb2.file_name =ta.file_name2
      left join tb tb3
      on    tb3.company = ta.company
        and tb3.file_name =ta.file_name3
where  (ta.file_name1 is not null and tb1.file_name is null)
      or  (ta.file_name2 is not null and tb2.file_name is null)
      or  (ta.file_name3 is not null and tb3.file_name is null)

追記：
質問を見返してみると結果を詰めていますね。
上記SQLではそこまで対応していないので、対応するとしたら、unionしたものをクロス集計ですね。

Comments

[asukara]

前回・今回同様どちらも回答いただきありがとうございます。
前回はファイル名を分解して取り出し、not exsistsで抽出するという糸口を見つけることができたので解決とさせていただきました。
結局のところ、サブクエリを利用してデータを整えた後、前回教えていただいたnot existsについてネット記事を漁りつつ解決することができました。
今週はずっとこちらのSQLを導き出せずに疲弊していたので本当に助かりました。
ありがとうございます!!