List:General Discussion« Previous MessageNext Message »
From:mikesz Date:January 1 2009 3:28am
Subject:Can a JOIN statement do this?
View as plain text  
Hello mysql and Happy New Year,

I am working with a Forum database. It contains a forums table, a
posts table and a threads table. Some of the posts contain flash
objects that I can find using a query like this one:

SELECT `pagetext`, `postid` FROM `post` WHERE `pagetext` LIKE
'%someuniqueidentifier%'ORDER BY `postid` DESC

This query works fine for what I needed. Now, the requirement has
changed to finding that latest object posted in a specific forum but
the forum table has no direct reference to the postid. The thread
table has a reference to the forumID but not a postID.

It looked something like this:
posts table:

+---------+------------+
| post_id | thread_id  |
+--------+-------------+

forum table:

+----------+------------+
| forum_id |            |
+----------+------------+

thread table:

+------------+----------+
| thread_id  | forum_id |
+------------+----------+

I know the forum ID that contains the objects but need to query the
threads to see which ones contain posts with objects. Then grab the
last one for processing.

So I think I have at least two queries now instead of the one I used to grab
that latest objects from the database.

If I did manual queries, I would select the latest thread_id posted to
forum_id and then a second query to find all the posts in that thread
that contained objects and grab the last one posted.

I think this all might be combined with a join but I am not clear
about how to do that because the conditional seems to need a result
that I don't have until I run the first query, i.e. WHERE
`forum_id`='163' AND `thread.thread_id`= (results of query to find
last thread) AND `pagetext` LIKE %someobjectidentifier% DESC LIMIT 1

So, I am still wondering if using JOIN is the right path to purse to
optimize this query.

Any suggestion greatly appreciated.


-- 
Best regards,
 mikesz                          mailto:mikesz@stripped

Thread
Can a JOIN statement do this?mikesz1 Jan
  • RE: Can a JOIN statement do this?Martin Gainty1 Jan