Left Join query causing 'Column not found' error In Drupal (v7.22) I'm trying to modify a query in a module so that it checks the value of an additional field from another table while looking for a result. I've been going through the documentation but cannot seem to stop my version causing an error. The original working code was:

$result = db_query("SELECT nid, type FROM {node}
WHERE type IN (:type) AND LOWER(title) = LOWER(:title)",
array(':type' =&gt; $node_types, 'title' =&gt; $page_name,));


I've then added in a left join:

$result = db_query("SELECT nid, type FROM {node} AS entity_id
LEFT JOIN {field_data_field_test}
AS nid ON node.nid = field_data_field_test.entity_id
WHERE node.type IN (:type) AND LOWER(node.title) = LOWER(:title)
AND field_data_field_test.field_test_value = :test_value",
array(':type' =&gt; $node_types, 'title' =&gt; $page_name, 'test_value' =&gt; $tvalue,));


But it gives the error:

PDOException: SQLSTATE[42S22]:
Column not found: 1054 Unknown column 'node.type' in 'where clause': ...


Any help would be appreciated.
You made 2 mistakes:


For complicated queries, use db_select(), not db_query(). That way you will use enough of built-in failsafes to know what exactly went wrong.
If you insist on db_query(), or need an UNION or something db_select() simply cannot create, never use unbracketed full table name. That's why aliasing exists.


Your query should look about like that:

$result = db_query("SELECT nid, type FROM {node} n
LEFT JOIN {field_data_field_test} f
ON n.nid = f.entity_id
WHERE n.type IN (:type) AND LOWER(n.title) = LOWER(:title)
AND f.field_test_value = :test_value",
array(':type' =&gt; $node_types, 'title' =&gt; $page_name, 'test_value' =&gt; $tvalue,));