Combine multiple MySql queries into a single query

I'm trying to solve the problem of the second day but with no success yet.

My goal is:

For certain taxonomy term ID (for example 1) I need to get at least one imagefield filepath of nodes, which belong to that term id (tid 1)

Below are three queries which give me what I need. All works fine but I know it looks really ugly and, I'm sure, there is a big performance issue.

$childterm = 10; // Taxonomy term ID
$result = db_fetch_array(db_query("SELECT node.vid FROM node JOIN 
                                   term_node ON  node.vid=term_node.vid WHERE  
       term_node.tid=$childterm AND 
                                   node.type= 'product' LIMIT 0,1"));           

$nvid = $result['vid']; // Extracting node VID by term ID that will be used futher 

$result = db_fetch_array(db_query("SELECT field_image_cache_fid FROM
                                   content_field_image_cache WHERE
       vid = '%d'", $nvid));

$fid = $result['field_image_cache_fid']; // Extracting file ID from array

$result = db_fetch_array(db_query("SELECT filepath FROM files WHERE
                                 开发者_如何学Go  files.fid = '%d'", $fid));           

$filepath = $result['filepath']; // Finally. Extracting file path from array

Please look at the picture.

How can I improve the query? Can I get filepath value using only ONE sql query?

Thanks in advance.

---

Something like this should work.

$sql = "SELECT f.filepath FROM {node} AS n
INNER JOIN {term_node} AS t ON t.nid = n.nid
INNER JOIN {content_field_image_cache} as c ON c.nid = n.nid
INNER JOIN {files} AS f on f.fid = c.fid
WHERE n.type = '%s'
AND t.tid = %d;"

$result = db_query($sql, $node_type, $tid);

Note that you should get the info on the content table name using

$db_info = content_database_info(content_fields($field_name));

The reason is because that the SQL will break if you switched from/to multiple values, unless you use this method to extra table and column names.

---

I accepted the answer from @googletorp. Although his solution was not 100% working for me, it pointed to the right direction.

Here is correct code:

$childterm = 10;// taxonomy term
$nodetype = 'product';// node type
$sql = "SELECT f.filepath FROM node n 
        INNER JOIN term_node tn ON tn.vid = n.vid  
        INNER JOIN content_field_image_cache cf ON cf.vid = n.vid 
        INNER JOIN files f ON f.fid = cf.field_image_cache_fid 
        WHERE n.type = $nodetype AND tn.tid = $childterm LIMIT 0, 1";
        $result = db_fetch_array(db_query($sql));
    $filepath = $result['filepath']; // image path

Thanks, you are all cool guys!