Page 1 of 1

Building query over 3 tables

Posted: Sun Jul 15, 2007 9:20 am
by SwizzCodes
Hi @all,

could someone help me to merge these two queries together, I can't by myself.

Getting the Topics

Code: Select all

SELECT 
								t.*,
								m.*,
								UNIX_TIMESTAMP(t.topic_time) as 'timestamp' 
							FROM
								topics AS t,
								members AS m
							WHERE
								t.topic_uid = m.member_id
Now I also want the newest Post in the topic:

Code: Select all

SELECT *, MAX(post_time) AS newest FROM posts GROUP BY post_tid
But how do these work together? I need to make some sort of subquery, but I just can't get it right. Later on I'll probably save the ID of the latest post in the topics table, but right now I need to do it this way.

Thanks in advance for any input!

Posted: Sun Jul 15, 2007 9:52 am
by feyd
How are they supposed to combine?

Why do they need to combine?

Posted: Sun Jul 15, 2007 10:22 am
by SwizzCodes
I'd like to have the latest post in each topic.

Posted: Sun Jul 15, 2007 11:37 am
by feyd
Your first query is retrieving all the posts already, though.

Posted: Sun Jul 15, 2007 12:18 pm
by SwizzCodes
Not really, it should look like this:

Code: Select all

GET ME
    all topics
WITH
    corresponding memberinfo of the topic owner/author
AND ALSO
    the postinfo from the newest post in the topic AND it's memberinfo of the post author.
now that would be a nice version of SQL :? :lol: