Trying to expand a query here

[Juan Pablo González]

Hey guys,

I'm trying to expand the functionality of the 'View unanswered posts' query, so it can show more unanswered posts than only those that have 0 replies.

My idea is this, look for all the topics that have > 0 replies, and in those, see if all the posters are the same (which would mean "bumps" by the OP).

I think I'm on the right track with this:

SELECT t.topic_id FROM topics t, posts p WHERE t.topic_id = p.topic_id AND t.topic_replies > 0 GROUP BY t.topic_id, p.poster_id

but what I need is to SELECT from this "intermediate" results, only those records in which t.topic_id appears ONCE.

Any ideas how to do that ?

So what I *think* I need is something like

SELECT * FROM (SELECT t.topic_id FROM topics t, posts p WHERE t.topic_id = p.topic_id AND t.topic_replies > 0 GROUP BY t.topic_id, p.poster_id) WHERE COUNT(topic_id) = 1

But it's not working (or it would be up and running now !!!)

 

[Juan Pablo González]

Ok, I think I got it:

SELECT t.topic_id, SUM(p.poster_id) sum1, SUM(t.topic_poster) sum2, COUNT(t.topic_id) total FROM topics t, posts p WHERE t.topic_id = p.topic_id AND t.topic_replies > 0 GROUP BY t.topic_id HAVING sum1/total = sum2/total

Kind of an ugly work-around, but hey, if it works, I'm happy !