(sorry about the goofiness of this reply... I subscribe to the digest version of the list, and it's a pain to reply to a single article.
> From: "District Webmaster" <[EMAIL PROTECTED]> > I have a table w/ quotes. Each quote has a quote_id (which is the table > key). Each quote also has quote_member_id (the id of the site member > who said the quote). _Some_ of the quotes are not "stand alone" -- they > only make sense as part of a series of quotes (a conversation or > statement/reply). Any quote that is part of a series of quotes also has > a quote_thread_id to specify that said quotes are grouped together. > (With me so far?) Here's what I think your table looks like (including some data) -- note that the headers are aliased, so they really have the names you specified. I'm also not retrieving the date column. mysql> select quote_id as id, quote_member_id as mem_id, quote_thread_id as th_id, quote_text from Quote; +----+--------+-------+------------------------------+ | id | mem_id | th_id | quote_text | +----+--------+-------+------------------------------+ | 1 | 1 | 3 | Member 1, no thread | | 2 | 1 | 4 | Another quote with no thread | | 3 | 2 | 1 | Quote in thread 1 | | 4 | 3 | 1 | Quote in thread 1 - 1 | | 5 | 2 | 2 | Quote thread 2 header | | 6 | 4 | 2 | Quote thread 2 member 4 | | 7 | 3 | 2 | Quote thread 2 member 3 | | 8 | 15 | 2 | Quote thread 2 member 15 | | 9 | 15 | 1 | Quote thread 1 member 15 | | 10 | 10 | 5 | No Thread Member 10 | +----+--------+-------+------------------------------+ 10 rows in set (0.00 sec) > I want to be able to retrieve all of the quotes by a specific member. > If they have any quotes that are part of a thread, I also need to > retreive quotes by other members with the same quote_thread_id (so that > you can read the original quote, in context). > > Is there any way to do this in a single query? I thought I might do > this with some sort of subselect, but the system I'm on has MySQL 4.013 > -- and according to the MySQL web site, subselects are not supported > until 4.1.0. (I have no control over the DB server version -- I am not > the server admin. I _DO_ have the ability, however, to redesign the > table if that would make the query easier.) LEFT JOIN (outer join) is your friend. The following query will retrieve what you want (of course, substitute your member ID for the 15 in this query): select b.quote_id, b.quote_member_id, b.quote_thread_id, b.quote_date, b.quote_text from Quote a LEFT JOIN Quote b on a.quote_thread_id = b.quote_thread_id where a.quote_member_id = 15 order by b.quote_thread_id, b.quote_date; Note: For this to work properly, you need to have a thread_id on EVERY message, whether or not it is it's own thread. Good Luck, and let me know if you have any additional questions. Ryan Erickson [EMAIL PROTECTED] http://www.ericksonfamily.com ____________________ BYU Unix Users Group http://uug.byu.edu/ ___________________________________________________________________ List Info: http://uug.byu.edu/cgi-bin/mailman/listinfo/uug-list
