CODEWITHSUNDEEP
mysqlsqldatabase
Reece McMillin
Reece McMillin

Reputation: 577

Selecting forum posts that have posts by two specific users - is there a better way?

I'm working on a personal project and want to gather all posts from a forum that contain comments from both users in a pair (in this case, those with IDs 7816 and 7424). I've accomplished this with the query below, but it's hamfisted and I'm convinced there's got to be a better way.

In plain English, the idea is to gather all the unique topic_id's that contain posts by 7816, then gather all the unique topic_id's that contain posts by 7424 WHERE the topic_id is somewhere in the set generated for 7816, then select * on that.

SELECT * FROM forums_posts WHERE topic_id IN (
    SELECT distinct topic_id
        FROM forums_posts WHERE topic_id IN (
            SELECT DISTINCT topic_id
                FROM forums_posts
                GROUP BY topic_id, author_id
                HAVING author_id = 7816
            )
        GROUP BY topic_id, author_id
        HAVING author_id = 7424
) ORDER BY topic_id, post_date;

I'm sure I could drop the complexity down considerably with some tricks I haven't learned yet. Is there an accepted way of performing operations like this?

Thanks!

Upvotes: 1

Views: 29

Answers (2)

Popeye
Popeye

Reputation: 35920

You can use the analytical function count as follows:

Select * from
(select fp.*,
       Count(distinct fp.author_id) over (partition by fp.topic_id) as cnt
  from forum_posts fp
 where fp.author_id in (7816, 7424) )
Where cnt = 2
Order by topic_id, author_id;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270391

One method is to use aggregation:

select fp.topic_id
from forum_posts fp
where fp.author_id in (7816, 7424)
group by fp.topic_id
having count(distinct fp.author_id) = 2;

If you want additional details, then use this as a subquery and join in tables with the additional information you want.

Upvotes: 2

Related Questions