MySQL Forums
Forum List  »  Newbie

Complex select involving two tables and distinct values from one
Posted by: Rob Saunders
Date: August 22, 2005 05:13PM

I have two tables.
f_cats (forum categories) and f_threads (forum threads)
I'm not actually creating forums right now, I'm just trying to figure out my own way of doing things with MySQL (I learn best by working with my own examples).

So anyway, the f_cats table simply looks like this:
id (int unsign)
title (varchar)

and the f_threads table looks like this:
id (int unsign)
time (int unsign)
parent (int)
title (varchar)
'time' is just a unix timestamp
'parent' identifies where the "post" will be, ex:
---------
if parent is -1, the "post" is a top level thread in the forum category with id 1
if parent is -2, the "post" is a top level thread in the forum category with id 2
if parent is 3 (not negative), the "post" is a reply to a thread with id 3
---------

So now, in these two tables, I have a couple entries. 2 "categories", and about 5 "threads."
What I want to figure out, is if it's possible, with one single query, to get each category, plus it's latest "post." I have figured out how to do this, but I need to get unique returns on the category.

I currently have this query:
'SELECT * FROM f_cats LEFT JOIN f_threads ON f_threads.parent*-1 = f_cats.id ORDER BY f_cats.id, f_threads.time DESC'
And it returns like this
| id | title | id | time | parent | title
| 1 | gen | 6 | 100 | -1 | this is another general post
| 1 | gen | 1 | 95 | -1 | this is a post to the general forum
| 2 | news | 4 | 100 | -2 | this is a post for the news forum
| 3 | admin | 10 | 93 | -3 | this is a post to the admin forum
(Sorry it's kind of hard to read)
As you can see, there are two returns for "gen" but I only want one (the most recent one). It's not a big deal now, but when it starts returning 100 returns instead of one, I think it might affect speed a little. Is what I'm looking to do possible? I tried a couple different things with DISTINCT, but couldn't get anythign to work right. Will I just have to code around it? I currently have two queries to accomplish what I'm trying, and it works okay.

Thanks for reading! Looking forward to any help you guys can provide!

Options: ReplyQuote


Subject
Written By
Posted
Complex select involving two tables and distinct values from one
August 22, 2005 05:13PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.