How to create temporary table and JOIN with node table in Drupal 7

Question

I have been working in a Drupal 7 web application.

Today I have tried to create a temporary table in my custom module using db_query_temporary function as follows:

$result = db_query_temporary("select * from {node}");

but $result gives "db_temporary_0". I need to JOIN a temporary table created from a complex query with {node} table.

Thanks in advance.

Answer 1

db_query_temporary() creates a temporary table and returns the table's name, so db_temporary_0 seems perfectly reasonable. You will use that result in your db_select() to join to whatever else you need:

 // obviously you wouldn't want something this simple....
 $tmp_tbl = db_query_temporary("SELECT * FROM {node}");
 $query = db_select('node', 'n');
 $query->join($tmp_tbl, 't', n.nid = t.nid'); //JOIN with node