Never used a UNION or a JOIN, but I need to

I’m using WordPress. I want to select a group of id’s in one table, and use those id’s to select a bunch of posts in another table, and only retrieve the most recent post.

So, theoretically, I want to do this:

$article_ids_query = mysql_query(“SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id='” . $term_taxonomy_id[0] . “'”); //this will retrieve a bunch of object_id's

$articles = mysql_query(“SELECT post_title, post_content FROM wp_posts WHERE ID='” . $article_ids_query . “’ LIMIT 1 ORDER BY post_date ASC”);

The obvious problem is that the $article_ids_query is going to be a result set.

How could I do it so that I use the query in $article_ids_query to be part of the $articles query.

This is what I could come up with using a “UNION”, but I’ve never used UNION before:

(SELECT object_id as a FROM wp_term_relationships WHERE term_taxonomy_id=‘" . $term_taxonomy_id[0] . "’) UNION (SELECT post_content, post_title FROM wp_posts WHERE ID=a AND post_type=‘post’) LIMIT 1 ORDER BY post_date ASC

Of course, I appreciate any and all help. Josh

SELECT p.post_title
     , p.post_content 
     , p.post_date
  FROM wp_term_relationships AS r
INNER
  JOIN ( SELECT id
              , MIN(post_date) AS first_post
           FROM wp_posts
         GROUP
             BY id ) AS m
    ON m.id = r.object_id
INNER
  JOIN wp_posts AS p
    ON p.id =  m.id
   AND p.post_date = m.first_post
 WHERE r.term_taxonomy_id = " . $term_taxonomy_id[0]

Josh, maybe use a plugin (http://wordpress.org/extend/plugins/recent-posts-plugin/)? I thought I had another plugin installed but guess not.

WOW. I don’t know how many yrs experience you have doing this stuff, but that query worked beautifully and I am impressed at it. Thank you.

thanks

:cool: