Newbie and Relational query

[Ronaldo]

OK I am creating a query to pull data from a Wordpress database.

I am trying to pull all POSTS (I can live with whole row from table "wp_posts" however ideally I only need 4 columns) that are (1) 'publish' and (2) '3'.

The '3' is the category of the post.

'publish' can be found in table "wp_posts.post_status" and '3' in table "wp_term_ralationships.term_taxonomy_id"

What connects them is "wp_posts.ID" = "wp_term_relationships.object_id"

I am a newbie and can handle simple, simple SELECT queries. I tried the JOIN tutorials but it's pretty over my head. Any help would be greatly appreciated. So far I have something like so, but no luck.

SELECT wp_posts.post_date, wp_posts.post_content, wp_posts.post_title, wp_posts.post_status, wp_term_relationships.object_id
FROM wp_posts,wp_term_relationships
WHERE wp_posts.ID = wp_term_relationships.object_id
ORDER BY wp_posts.post_date DSC;

Oh and this is all done in php

mysql_select_db($database_theGREENbar, $theGREENbar);
$query_recordNEWS = "SELECT wp_posts.post_date, wp_posts.post_content, wp_posts.post_title, wp_posts.post_status, wp_term_relationships.object_id
FROM wp_posts,wp_term_relationships
WHERE wp_posts.ID = wp_term_relationships.object_id
ORDER BY wp_posts.post_date DSC;";
$recordNEWS = mysql_query($query_recordNEWS, $theGREENbar) or die(mysql_error());
$row_recordNEWS = mysql_fetch_assoc($recordNEWS);
$totalRows_recordNEWS = mysql_num_rows($recordNEWS);


Redards,

Ronaldo

[Torque]

Joins typically look something like this...

SELECT x.*
     , y.*
     , z.*
  FROM table1 x
  JOIN table2 y
    ON y.some_id = x.some_id
  JOIN table3 z
    ON z.some_id = x.some_id
 WHERE x.some_other_thing = 'a'
   AND y.some_thing_else = 'b'
   AND z.one_more_thing = 'c';

(I'll probably be reprimanded for using '*' instead of naming the columns, but hey)

[UserName]

Originally Posted by Torque

(I'll probably be reprimanded for using '*' instead of naming the columns, but hey)

no, we all understand that if your focus is on the joins, the SELECT clause is of minor importance

what i would reprimand you for, though, is the use of the unqualified "JOIN"

i'm a firm believer that you should indicate LEFT OUTER, INNER, RIGHT OUTER, FULL OUTER, or CROSS, and not leave it to the mysql default

in fact, in standard sql, the keyword INNER is ~not~ optional

[Ronaldo]

Thank you very much. Because of your help I was able to figure it out. here is what i have.

SELECT wp_posts.*, wp_term_relationships.*
FROM wp_posts JOIN wp_term_relationships on wp_term_relationships.object_id = wp_posts.ID
WHERE wp_posts.post_status = 'publish' AND wp_term_relationships.term_taxonomy_id = '3'

if this is in any way incorrect or inefficient syntax please I am always open to improving my code.

[Ronaldo]

Originally Posted by UserName

no, we all understand that if your focus is on the joins, the SELECT clause is of minor importance

what i would reprimand you for, though, is the use of the unqualified "JOIN"

i'm a firm believer that you should indicate LEFT OUTER, INNER, RIGHT OUTER, FULL OUTER, or CROSS, and not leave it to the mysql default

in fact, in standard sql, the keyword INNER is ~not~ optional

I dont quite follow but an explanation on what you mean here would be freat in me understanding what you mean by the terms you use and what an unqualified JOIN is?

[BoD - Chris]

Originally Posted by Ronaldo

I dont quite follow but an explanation on what you mean here would be freat in me understanding what you mean by the terms you use and what an unqualified JOIN is?

In your query you've only used JOIN. Using INNER JOIN in place of it you will quickly be able to scan the query and know it is using an INNER JOIN rather than wondering what the default JOIN type is in MySQL.
And as "UserName" says, the standard requires that the word INNER is there, so it is a good habit to get in to.

[Ronaldo]

Originally Posted by BoD - Chris

In your query you've only used JOIN. Using INNER JOIN in place of it you will quickly be able to scan the query and know it is using an INNER JOIN rather than wondering what the default JOIN type is in MySQL.
And as "UserName" says, the standard requires that the word INNER is there, so it is a good habit to get in to.

Thanx , I will make that adjustment