a series of linked queries

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
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

a series of linked queries

Post by Unipus »

Sorry guys, but it's been a few months since I needed to do anything beyond basic SQL, and my brain's gone a bit foggy on it.

I have three tables. For a single query, I need to pull data from all three. Worst case scenario, I know how to get the results I want using 5 queries. But... *5 queries*? No.

The three tables are posts, threads, and users. 'posts' references 'threads' and 'users' by foreign keys. For this query, I need to summarize threads. So the data I need is:

name of the thread (contained in 'threads')
date started (contained in 'posts')
date last updated (contained in 'posts')
name of the user who started it (contained in 'users')
name of the user who updated later (contained in 'users')

Now, the horribly inefficient way of doing this is to query 'threads' first for the summary of titles, then for each result there query 'posts' for the start date, and then query 'users' for the username, then query 'posts' for the end date and 'users' again for the username that time. But that's a big mess.

So what's a better way to do things?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

think you have to lookup: inner join, group by, and max/min functions
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post by Unipus »

I have looked up and used those functions in the past. I'm just drawing a bit of a blank now on how the puzzle pieces fit together. But maybe I just need a nap.
Post Reply