SQL JOIN SYNTAX?!

[jhwilliams]

This question pertains to my Wordpress database. I need to JOIN 3 SQL tables to extract information from the wp_comments table.


dbname: wordpress

table: wp_comments
field: ID
field: post_name

table: wp_post2cat
field: post_id
field: category_id

table: wp_categories
field: category_nicename
field: cat_ID


The idea is that wp_comments.ID = wp_post2cat.post_id, and wp_post2cat.category_ID = wp_categories.cat_ID

I need a properly syntaxed SQL query written as a string for a php variable, $query. I've written the gernal idea out below, where things inside [[ ]] need to be put into proper SQL

$query="SELECT [[wp_comments.ID, wp_comments.post_name]] FROM [[all three tables]] WHERE [[wp_categories.category_nicename=SomeCategoryName]] ORDER [[by wp_comments.ID, ascending]] LIMIT [[to $delimiter, a php variable]]"

If you are an SQL buff and have a spare moment, I'd REALLY apreciate a moment of your time to help me out here! Thanks so much - Jameson

 

[r937]

you. were. so. close.

SELECT wp_comments.ID
     , wp_comments.post_name
  FROM wp_categories
INNER
  JOIN wp_post2cat  
    ON wp_post2cat.category_ID 
     = wp_categories.cat_ID
INNER
  JOIN wp_comments   
    ON wp_comments.ID 
     = wp_post2cat.post_id
 WHERE wp_categories.category_nicename 
     = 'miscellany'
ORDER 
    BY wp_comments.ID asc LIMIT 10,10

please note that everything up to the LIMIT is ANSI SQL

mysql sort of supports ANSI SQL, mostly, but in this particular case, mysql supports INNER JOIN syntax with no difficulty

r937.com | rudy.ca