SQLで欲しい情報を取得するためのクエリが分からない

Question

テーブル構造としてはシンプルなidだけを持ったUsersテーブルとPostsテーブルで、中間テーブルとしてUserPostsがあるとします。また、このようなデータが入っているとします

mysql> select * from user_posts;
+----+---------+---------+---------------------+---------------------+
| id | user_id | post_id | created_at          | updated_at          |
+----+---------+---------+---------------------+---------------------+
|  1 |       1 |       1 | 2021-04-22 09:11:59 | 2021-04-22 09:11:59 |
|  2 |       1 |       2 | 2021-04-22 09:11:59 | 2021-04-22 09:11:59 |
|  3 |       2 |       2 | 2021-04-22 09:11:59 | 2021-04-22 09:11:59 |
|  4 |       1 |       3 | 2021-04-22 09:11:59 | 2021-04-22 09:11:59 |
|  5 |       2 |       3 | 2021-04-22 09:11:59 | 2021-04-22 09:11:59 |
|  6 |       3 |       3 | 2021-04-22 09:11:59 | 2021-04-22 09:11:59 |
+----+---------+---------+------------+---------------------+---------------------+

mysql> select * from posts;
+----+---------------------+---------------------+
| id | created_at          | updated_at          |
+----+---------------------+---------------------+
|  1 | 2021-04-22 09:11:59 | 2021-04-22 09:11:59 |
|  2 | 2021-04-22 09:11:59 | 2021-04-22 09:11:59 |
|  3 | 2021-04-22 09:11:59 | 2021-04-22 09:11:59 |
+----+---------------------+---------------------+

mysql> select * from users;
+----+---------------------+---------------------+
| id | created_at          | updated_at          |
+----+---------------------+---------------------+
|  1 | 2021-04-22 09:11:59 | 2021-04-22 09:11:59 |
|  2 | 2021-04-22 09:11:59 | 2021-04-22 09:11:59 |
|  3 | 2021-04-22 09:11:59 | 2021-04-22 09:11:59 |
+----+---------------------+---------------------+

上記の条件のもと、user_posts.user_idが2のpostを除いたposts一覧を取得したい(上記の条件だとPostID=1のものだけが帰ってきて欲しい)のですがいい方法が思い付かず、もしわかる方がいらっしゃいましたら教えていただきたいです。

追記

当初 select * from posts inner join user_posts on posts.id = user_posts.post_id where not user_posts.user_id = 2;というクエリで取得できると思っていたのですが、これだと以下のような問題点があり意図したものが取得できませんでした

• user_id=1, post_id=2の場合やuser_id=3, post_id=3の場合がすり抜けてしまう(postID=2, 3はuser_id=2が持ってるPostIDなので除外したい)
• user_id=1, post_id=3やuser_id=3, post_id=3のものは同じpost_idなので重複しないようにしたい

Comments

[m.ts10806]

まず自身が思ったように組んでみてください。
あと、テーブル定義をCREATE TABLE文で提示してください。

Answer 1

for_spさんのやりたいこととしては、
user.idが2であるユーザーに送信されていないpostを見つけたいになるんですかね。
それだと上のneko_the_shadowさんのクエリでも取得できると思いますし、下記のクエリでも実現できると思います。

SELECT *
FROM posts 
WHERE id NOT IN (
      SELECT post_id
      FROM user_post
      WHERE user_id = 2
     )

サブクエリが評価された後は次のようなクエリになります。

SELECT *
FROM posts 
WHERE id NOT IN (2,3)

ここまでくるとわかりやすいですかね。
最終的にpostテーブルのidが2,3以外のもの、ここだとid=1のレコードが取得できることになります。

Comments

[退会済みユーザー]

なるほど！INを使うのですね、、！勉強になります！ありがとうございます！

Answer 2

やりたいことは以下であっていますか?

• 欲しいのはpostsテーブルのデータ
• user_postsを見て、user_id=2がかかわっているデータは不要

であれば、以下のようなクエリになると思います。

SELECT *
FROM posts p
WHERE NOT EXISTS (
  SELECT *
  FROM user_posts up
  WHERE p.id = up.post_id
  AND up.user_id = 2
)

Comments

[退会済みユーザー]

あっています！EXISTSを使う方法があるのですね、、！ありがとうございます！