querying multiple tables--where to start?

[klitscher]

Hello,

I am writing some code (using php) to create a website that will allow users to post news and events and am coming up with the MYSQL backend now.  I am using 4 tables to start....one will contain the users info, one will contain the posts, one will contain the different groups the posts will be organized by (news, Family events, sports, etc) and the final one will be a linking table between the groups and the posts containing two fields, group_id and post_id (this is because each post can be filed under multiple groups).  So, what I want to do is select the 5 most recent posts (or the five closest upcoming events) of any given group and display the information, including info about the user who posted it and I don't know where to start.

I would assume I would first query the group_post_link table to find all of the posts under a certain group, then query the post table to find the 5 applicable posts, and then query the user table to get all of the information...but I'm not sure where to start or what would be the best (read: fastest and easiest, not necessarily in that order) way to do it.  I've tried mysql.org, but I'm not sure what I should be looking for.  Any help would be greatly appreciated.

[COBOLdinosaur]

Assuming the posts table has the user id associated with it then you should be able to select the user infomation and post information in a single query by crossing the table on the user id key.  So if you select the applicable columns from the user and posts table where the user id on the user table and post table match and the post id matches the cross-reference in the group/posts table, then you simply order on the timestamp, and use a limit of 5 to only return the first 5 rows of the data set.

[klitscher]

Thanks...that helped out a lot and I've figured out how to do it...I just didn't know what terms to google, and cross join and query multiple tables did it...Thanks again

Ken

[COBOLdinosaur]

Yeah, finding the right search term is sometimes the hardest part of solving a problem.  Glad I could help.