Help With SQL Query

Question

I have the following code:

$my_result = db_query("SELECT nid FROM content_field_taxo_listing WHERE field_taxo_listing_value = '%s'", $tid);

while ($my_row = db_fetch_array($my_result)) {
  $nodeids[] = $my_row['nid'];
}

What I want to do is to join (it might be a union, but I am not sure) the "content_field_taxo_listing" table with the "node" table, and order the "nids" field by title in the "node" table.

What is the Drupal way for doing this?

Answer 1

Try this one:

$my_result = db_query("
  SELECT ctl.nid 
  FROM {content_field_taxo_listing} ctl 
  LEFT JOIN {node} n ON ctl.nid = n.nid
  WHERE ctl.field_taxo_listing_value = :tid 
  ORDER BY n.title ASC",
  array(":tid"=>$tid)
);

Answer 2

$my_result = db_query("
  SELECT {content_field_taxo_listing}.nid 
  FROM {content_field_taxo_listing} LEFT JOIN {node} ON content_field_taxo_listing.nid = node.nid
  WHERE content_field_taxo_listing.field_taxo_listing_value = '%s'
  ORDER BY {node}.title", $tid);