Topic: News query-question

I've been working on a news-function (yeah, me too :)). There's no problem making one with two querys, but can it be done using only one?

SELECT t.*, p.message FROM topics AS t, posts AS p WHERE p.topic_id=t.id

This query obviously returns one row for each post in the topic. Is there a way to prevent this from happening? I only want the first post for each topic, not all of them.

Re: News query-question

Hey there!

When you say news function, is it something similar to PunNewz (search in Mod Releases)?

You can retrieve first row only by adding ORDER BY p.id LIMIT 1 to your query ...

Digital photography news, reviews, discussions and more!
http://www.shuttertalk.com

The online bible for all
http://www.publicbible.com

Re: News query-question

This should do the trick:

SELECT t.*, p.message FROM topics AS t INNER JOIN posts AS p ON p.topic_id=t.id GROUP BY t.id
"Programming is like sex: one mistake and you have to support it for the rest of your life."

Re: News query-question

cuteseal wrote:

Hey there!

When you say news function, is it something similar to PunNewz (search in Mod Releases)?

You can retrieve first row only by adding ORDER BY p.id LIMIT 1 to your query ...

Yeah, it is. But I want to search the topics too, and with only one post.

Rickard wrote:

This should do the trick:

SELECT t.*, p.message ...p ON p.topic_id=t.id GROUP BY t.id

Ohh, so that's what 'GROUP BY' is used for :) Makes this much easier, thanks!

Re: News query-question

I suppose you could use the punbb search function and make it search particular forum(s)...

Digital photography news, reviews, discussions and more!
http://www.shuttertalk.com

The online bible for all
http://www.publicbible.com

Re: News query-question

cuteseal wrote:

I suppose you could use the punbb search function and make it search particular forum(s)...

I was a bit unclear there. I don't want to search anything, just list the topics in a forum (including the first post).

But the problem is solved now when Rickard showed be the secret trick (GROUP BY).

Re: News query-question

wooo... secret! smile

Digital photography news, reviews, discussions and more!
http://www.shuttertalk.com

The online bible for all
http://www.publicbible.com