时间戳

首页 > 解决方案 > SQL Where at least all specified

sql - SQL Where at least all specified

问题描述

I have a query that returns a list of threads. Each thread can have multiple posts attached to them, and each post can have multiple tags attached.

Consider the following data,

INSERT INTO thread(id, title) VALUES (1, 'Thread A');
INSERT INTO thread(id, title) VALUES (2, 'Thread B');
INSERT INTO thread(id, title) VALUES (3, 'Thread C');

INSERT INTO post(id, body, thread_id) VALUES(1, 'This is some example text', 1);
INSERT INTO post(id, body, thread_id) VALUES(2, 'This is some example text', 1);
INSERT INTO post(id, body, thread_id) VALUES(3, 'This is some example text', 2);
INSERT INTO post(id, body, thread_id) VALUES(4, 'This is some example text', 3);

INSERT INTO tag(id, name) VALUES(1, 'Funny');
INSERT INTO tag(id, name) VALUES(2, 'Boring');
INSERT INTO tag(id, name) VALUES(3, 'Interesting');
INSERT INTO tag(id, name) VALUES(4, 'Weird');

INSERT INTO tag_post_map(id, tag_id, post_id) VALUES (1, 1, 1);
INSERT INTO tag_post_map(id, tag_id, post_id) VALUES (2, 3, 2);
INSERT INTO tag_post_map(id, tag_id, post_id) VALUES (3, 1, 3);
INSERT INTO tag_post_map(id, tag_id, post_id) VALUES (4, 3, 3);
INSERT INTO tag_post_map(id, tag_id, post_id) VALUES (5, 2, 4);
INSERT INTO tag_post_map(id, tag_id, post_id) VALUES (6, 1, 4);

I want to get all threads that have posts with the tags "Funny" and "Interesting" grouping the tags by post, however I do not wish to exclude posts that have more tags, as long as they have those two. That means, I wish to query for AT LEAST the tags that I need for any given thread. "Thread C" should not show up, because it only has a post with the "Funny" and "Boring" tags, not also the required "Interesting" tag.

This is my query currently

SELECT thread.id, thread.title from thread where exists (
  select *
  from tag_post_map
  inner join post on post.id = tag_post_map.post_id
  where
    tag_post_map.tag_id in (1, 3)
    and post.thread_id = thread.id
);

This also returns "Thread C", which is expected but not what I want. I am not able to figure out how to achieve the desired result.

And here's an SQL fiddle.

标签： sqlpostgresql

解决方案

I want to get all threads that have posts with the tags "Funny" and "Interesting" grouping the tags by post,

You can approach this using aggregation and a having clause:

select p.thread_id
from posts p join
     tag_post_map tpm
     on tpm.post_id = p.id join
     tags t
     on tpm.tag_id = t.id
where tpm.name in ('Funny', 'Interesting')
group by p.thread_id
having count(distinct tpm.name) = 2;

Note: The threads table is not needed. In addition, the tags could be on different posts for the thread, which appears to be your intention. Because multiple posts can have the same tag, you cannot use count(*).

推荐阅读