CODEWITHSUNDEEP
sqlpostgresqlself-join
Steve
Steve

Reputation: 1019

What am I doing wrong in this SQL self join

I have a table with blog posts assigned to topics with records like so:

id blogid postid topic subtopic
81829 1 604136 foo 1
81830 60 604139 foo 1
81831 1 604144 foo 1
81832 1 604144 bar 2
81833 88 604146 foo 1
81834 88 604146 bar 2
81835 88 604146 bar 7
81836 51 604152 foo 1
81837 71 604156 foo 1
81838 75 604160 foo 18
81839 75 604160 foo 8
81840 75 604160 foo 1
81841 75 604160 foo 12
81842 75 604160 foo 16
81843 75 604160 bar 2
81844 75 604160 bar 7
81845 118 604162 foo 18
81846 118 604162 foo 8
81847 118 604162 foo 1
81848 118 604162 foo 13

I want to get only postids that have a mix of the two topics. So I want a query that returns the records for postids 604144, 604146, and 604160 only. I think what I need for this purpose is a self join and I've tried this one:

select * from topics A, topics B where A.postid = B.postid and A.topic like 'foo' and B.topic like 'bar'

But that just gives me duplicates of postids on one topic. What am I doing wrong?

Upvotes: 0

Views: 79

Answers (1)

9bO3av5fw5
9bO3av5fw5

Reputation: 998

There's a little ambiguity in the topic criteria but something more like this perhaps?

SELECT postid
FROM topics
GROUP BY postid
HAVING COUNT(DISTINCT topic) > 1;

Upvotes: 4

Related Questions