Drupal has an inconsistent data structure for CCK fields: if a field is only in one content type, it's stored in a content_type_XX table as a column, but if it's shared across multiple content types, it moves to its own content_field_XX column. Views figures out where fields are located automatically, but in custom SQL queries, this can be a real pain - you can write a query that works one day, then share a field, and the query breaks.

I asked for a solution to this on IRC and was pointed to this post by drewish, Correctly accessing CCK fields in SQL queries. I adapted that method a little to create this helper function:

/**
* function to get the TABLE or COLUMN for a CCK FIELD
* method adapted from http://drewish.com/content/2010/06/correctly_accessing_cck_fields_in_sql_queries
*
* @param $field_name
* @param $type 'table' or 'column'
*
* to get both table and column, run function twice with each $type
*/
function helper_cck_field_sql($field_name, $type) {
$field = content_fields($field_name);
$db_info = content_database_info($field);
 
if ($type == 'table' && isset($db_info['table'])) {
return $db_info['table'];
}
elseif ($type == 'column' && isset($db_info['columns']['value']['column'])) {
return $db_info['columns']['value']['column'];
}
return NULL;
}