Drupal performing a query against the database

I wish to retrieve some nids from my drupal database. I have a query that I wish to run.

  SELECT node.nid AS projectnid
            FROM node node
            INNER JOIN content_type_project node_data_field_project_client ON node.vid = node_data_field_project_client.vid
            WHERE node_data_field_project_client.field_project_client_nid = (SELECT node_data_field_profile_company.field_profile_company_nid AS company_nid
            FROM node node  LEFT JOIN content_type_profile node_data_field_profile_company ON node.vid = node_data_field_profile_company.vid WHERE node.nid = 218)

I am calling the query by using:

$query =
        "
            SELECT node.nid AS projectnid
            FROM node node
            INNER JOIN content_type_project node_data_field_project_client ON node.vid = node_data_field_project_client.vid
            WHERE node_data_field_project_client.field_project_client_nid = (SELECT node_data_field_profile_company.field_profile_company_nid AS company_nid
            FROM node node  LEFT JOIN content_type_profile node_data_field_profile_company ON node.vid = node_data_field_profile_company.vid WHERE node.nid = 218)
        ";
$result = db_query($query);
dsm($result);

The dsm is giving me an empty object. When I run the SQL directly I get back a result.

So my question would be how would you get db_query to return you all your results as an object (I don'开发者_运维技巧t really mind if an object or array).

(The SQL was created by looking at the query output for views.)

This is a follow up to question: Drupal Views Relationships and Arguments

I have a Person content type. It has a node reference field of a company which is also a content type. I then have a content type called Project. A project has a node reference to a company content type. I want to list all the projects related to a person id (nid)id (nid)

The following works:

$query =
        "
            SELECT node.nid AS projectnid
            FROM node node
            INNER JOIN content_type_project node_data_field_project_client ON node.vid = node_data_field_project_client.vid
            WHERE node_data_field_project_client.field_project_client_nid = (SELECT node_data_field_profile_company.field_profile_company_nid AS company_nid
            FROM node node  LEFT JOIN content_type_profile node_data_field_profile_company ON node.vid = node_data_field_profile_company.vid WHERE node.nid = 218)
        ";
$results = db_query($query);
while ($result = db_result($results)) {
    dsm($result);
}

You need to use db_result() to get the results out. Worked this out by using http://drupal.org/node/259432#comment-846946