Building query over 3 tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
SwizzCodes
Forum Newbie
Posts: 20
Joined: Tue Dec 19, 2006 3:23 pm

Building query over 3 tables

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

How are they supposed to combine?

Why do they need to combine?
SwizzCodes
Forum Newbie
Posts: 20
Joined: Tue Dec 19, 2006 3:23 pm

Post by SwizzCodes »

I'd like to have the latest post in each topic.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Your first query is retrieving all the posts already, though.
SwizzCodes
Forum Newbie
Posts: 20
Joined: Tue Dec 19, 2006 3:23 pm

Post 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:
Post Reply