Page 1 of 1

a series of linked queries

Posted: Wed Apr 14, 2004 7:37 pm
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?

Posted: Wed Apr 14, 2004 8:00 pm
by timvw
think you have to lookup: inner join, group by, and max/min functions

Posted: Wed Apr 14, 2004 8:04 pm
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.