Multiple Joins, not sure how

[bdichiara]

Here's the deal. I'm modifying a WordPress theme to suit my needs and i'm struggling with the way WP has setup their database. I'm trying to setup a Query that will match a Post with it's Category ID, then match the category id with the category name. I need some sort of INNER or LEFT join in there, but I don't know which type of Join to use.

here's what i have

SELECT *, DATE_FORMAT($wpdb->posts.post_date,'%c.%e.%Y') AS post_date2 FROM $wpdb->posts LEFT JOIN $wpdb->post2cat ON $wpdb->posts.ID = $wpdb->post2cat.category_id INNER JOIN $wpdb->categories ON $wpdb->post2cat.category_id = $wpdb->categories.cat_ID WHERE $wpdb->posts.post_date < '$now' AND $wpdb->posts.post_status = 'publish' ORDER BY $wpdb->posts.post_date DESC

I'm pretty sure the first LEFT JOIN is right, but I don't think the INNER JOIN is right. any thoughts?

_______________
_brian.

 

[r937]

LEFT OUTER JOIN is correct if you wish to retrieve posts that have no matching category

if you do, then the second join must also be a LEFT OUTER JOIN

also, i'm going to make a guess that you weren't getting any results, because you were matching posts.id to post2cat.category_id, which is wrong

finally, please, please, please don't use the dreaded, eveil "select star"

SELECT $wpdb->posts.ID
     , $wpdb->categories.cat_ID
     , DATE_FORMAT($wpdb->posts.post_date,'%c.%e.%Y') 
             AS post_date2 
  FROM $wpdb->posts 
LEFT OUTER
  JOIN $wpdb->post2cat 
    ON $wpdb->post2cat.[b]post[/b]_id = $wpdb->posts.ID  
LEFT OUTER 
  JOIN $wpdb->categories 
    ON $wpdb->categories.cat_ID = $wpdb->post2cat.category_id
 WHERE $wpdb->posts.post_date < '$now' 
   AND $wpdb->posts.post_status = 'publish' 
ORDER 
    BY $wpdb->posts.post_date DESC

r937.com | rudy.ca

 

[bdichiara]

Well, thanks rudy. I knew you'd come through, but what I ended up doing (and I don't know why I didn't think of it before) was I found another query in the WP source that matched up the Post with the Category, and just used a section of that, which is:

SELECT *, DATE_FORMAT($wpdb->posts.post_date,'%c.%e.%Y') AS post_date2 FROM $wpdb->posts LEFT JOIN $wpdb->post2cat ON ($wpdb->posts.ID = $wpdb->post2cat.post_id) LEFT JOIN $wpdb->categories ON ($wpdb->post2cat.category_id = $wpdb->categories.cat_ID) WHERE $wpdb->posts.post_date < '$now' AND $wpdb->posts.post_status = 'publish' ORDER BY $wpdb->posts.post_date DESC

So thanks for the help. I'm sure this means just about the same as what you posted.

_______________
_brian.