Problem in making a query using subqueries....

[vikram8jp]

MySql server version: 5.0.67

 

MySql code:

 to be figured out 

 

MySql Errors: NA

 

Table structure:

CREATE TABLE `topics` (

`topic_id` bigint(20) unsigned NOT NULL auto_increment,

`subject` text collate utf8_unicode_ci NOT NULL,

PRIMARY KEY  (`topic_id`)

) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

 

CREATE TABLE `posts` (

`post_id` bigint(20) unsigned NOT NULL auto_increment,

`topic_id` bigint(20) unsigned NOT NULL auto_increment,

`post_time` TIMESTAMP,

`body` text collate utf8_unicode_ci NOT NULL,

PRIMARY KEY  (`post_id`)

) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

 

Explain Output of query: NA

 

Description:

I am making a simple bulletin board in my application. I want to find out the last post in every topic and display the time in front of topic.

 

My problem:

Consider this data:

 

topic ---------------------------- topic_id

================================================

what is mysql ----------------- 1

what is php -------------------- 2

================================================

 

body ---------------------------- post_id ------- topic_id ------- post_time

=========================================================

mysql is database ------------ 1 --------------- 1 --------------- 22/10/09 11:30 AM

mysql is cool  ------------------ 2 --------------- 1 --------------- 22/10/09 12:10 PM

mysql is great ----------------- 3 --------------- 1 --------------- 22/10/09 13:15 PM

 

php is web language -------- 4 --------------- 2 --------------- 13/11/09 09:30 AM

php is too cool ---------------- 5 --------------- 2 --------------- 13/11/09 11:10 AM

I like php ----------------------- 6 --------------- 2 --------------- 13/11/09 11:30 AM

==========================================================

 

The query should return this.....

 

topic ---------------------------- topic_id ------- last_post

================================================

what is mysql ----------------- 1 --------------- 22/10/09 13:15 PM

what is php -------------------- 2 -------------- 13/11/09 11:30 AM

================================================

 

What I've tried:

 

SELECT * 
FROM  `qa_sessions_tb` q1
INNER JOIN  `qa_posts_tb` q2
USING ( session_id ) 
GROUP BY q1.session_id
HAVING q2.posts_id = ( 
SELECT posts_id
FROM qa_posts_tb
GROUP BY session_id
HAVING posted_at = MAX( posted_at ) ) 
LIMIT 0 , 30

 

This gave wrong result.

 

Can somebody help me out? How do you think phpfreaks displays last post on forum home page?

 

Thanks and Regards

Vikram

 

[vikram8jp]

Let it be I have solved it. Never get any answers here anyway. 

 

Here is the query I was looking for. It's damn easy, just didn't strike me.

 

SELECT t.topic, t.topic_id, p.post_id, p.post_time
FROM topics t INNER JOIN posts p
ON t.topic_id = p.topic_id
GROUP BY t.opic_id

[corbin]

Never get any answers here anyway?

 

58 minutes is too long to wait?

[vikram8jp]

You missed the AM/PM difference there, corbin.  It was 12 hours 58 minutes.

 

Anyway, the way I solved it was also wrong....

 

This is wrong.... I missed the MAX() aggregate function first of all... my apologies.

 

SELECT t.topic, t.topic_id, p.post_id, [u][b]MAX[/b][/u](p.post_time)
FROM topics t INNER JOIN posts p
ON t.topic_id = p.topic_id
GROUP BY t.opic_id

 

Secondly, that too doesn't give absolutely correct result.

 

My problem:

Consider this data:

 

topic ---------------------------- topic_id

================================================

what is mysql ----------------- 1

what is php -------------------- 2

================================================

 

body ---------------------------- post_id ------- topic_id ------- post_time

=========================================================

mysql is database ------------ 1 --------------- 1 --------------- 22/10/09 11:30 AM

mysql is cool  ------------------ 2 --------------- 1 --------------- 22/10/09 12:10 PM

mysql is great ----------------- 3 --------------- 1 --------------- 22/10/09 13:15 PM

 

php is web language -------- 4 --------------- 2 --------------- 13/11/09 09:30 AM

php is too cool ---------------- 5 --------------- 2 --------------- 13/11/09 11:10 AM

I like php ----------------------- 6 --------------- 2 --------------- 13/11/09 11:30 AM

==========================================================

 

The query should return this.....

 

topic ---------------------------- topic_id ------- post_id ------ last_post

=====================================================

what is mysql ----------------- 1 --------------- 3 -------------- 22/10/09 13:15 PM

what is php -------------------- 2 --------------- 6 -------------- 13/11/09 11:30 AM

======================================================

 

Its giving....

 

topic ---------------------------- topic_id ------- post_id ------ last_post

=====================================================

what is mysql ----------------- 1 --------------- 1 -------------- 22/10/09 13:15 PM

what is php -------------------- 2 --------------- 4 -------------- 13/11/09 11:30 AM

======================================================

 

Can anybody help me out with this?

 

Thanks and Regards

Vikram

[corbin]

This is basically a guess, but try:

 

 

SELECT t.topic, t.topic_id, p.post_id, MAX(p.post_time)

FROM topics t INNER JOIN posts p

ON t.topic_id = p.topic_id

GROUP BY t.topic_id, p.post_id;

 

 

 

The problem is because of the group by, so grouping by another column might make it work.

[vikram8jp]

Thanks Corbin

 

That didn't work correctly. This query displays all the records. It does not do something like displaying one post per topic, let alone finding the post which has maximum post time.  ???

Really, this query seems quite do-able and the logic seems to be around the corne.  ???

 

This is basically a guess, but try:

 

 

SELECT t.topic, t.topic_id, p.post_id, MAX(p.post_time)

FROM topics t INNER JOIN posts p

ON t.topic_id = p.topic_id

GROUP BY t.topic_id, p.post_id;

 

 

 

The problem is because of the group by, so grouping by another column might make it work.

[corbin]

Ahhh, yeah, I should have seen that one coming... since that's the whole point of group by.

 

 

 

Hrmmm...  A sub query would work, but the performance on that would not be optimal.

 

 

A cheap hack would be to store the last activity date in the topics table and just update it when ever something happens as far as posts go.

[Phoenix~Fire]

select forum.*,count(forum_content.id) as topics from forum left join forum_content on forum_content.forum=forum.id group by forum.id;

 

that's code I just used in my forum, seems to work quite well... I'm pretty sure you need pretty much the same thing.  I'm lazy, so you'll have to modify it for your script on your own. 

 

PS

[vikram8jp]

@Phoenix~Fire

 

Hello there. Your query will work fine man, but only in the case of COUNT aggregate function. If I replace COUNT with MAX then it will cause the problem that I have stated in my third post of this topic.

 

Thanks and Regards

Vikram

 

select forum.*,count(forum_content.id) as topics from forum left join forum_content on forum_content.forum=forum.id group by forum.id;

 

that's code I just used in my forum, seems to work quite well... I'm pretty sure you need pretty much the same thing.  I'm lazy, so you'll have to modify it for your script on your own. 

 

PS

[Phoenix~Fire]

Interesting.... I have MAX working just fine using 3 joined tables, as well as count, greater and a whole bunch of other aggrevate functions.  Basically I got bored 

 

Try:

select topics.*,max(posts.post_time) as time from topics left join posts on posts.topic_id=topics.topic_id group by topic.id;

 

Works fine on my DB.