Please help with this query! Need to get taxonomy terms of related nodes

Question

I've inherited this code that runs on our home page. It's used to do a custom display of specific pieces of related info. Here's how things are set up:

We have an article. It has a series of node reference fields that allow us to link related images (actually nodes themselves, which contain a headshot and some text; it's not an actual attached image.) Each of those headshots has a thumbnail created via ImageCache so that we can display a small version in search results and on list-type pages.

We have a new requirement that if the related image node for a specific article has a certain taxonomy term (tid) associated with it, it should display differently than other images.

The existing query (which I did not write) looks like this:

SELECT DISTINCT n.title, nr.teaser, n.nid, DATE_FORMAT(FROM_UNIXTIME(n.created), '%M %e, %Y') AS date, f.filepath AS image, cfri.field_related_images_nid as image_id 

FROM node n JOIN node_revisions nr ON n.nid = nr.nid 
LEFT JOIN content_field_related_images cfri ON (n.nid = cfri.nid AND cfri.delta = 0) 
LEFT JOIN content_field_att_file cfaf ON cfri.field_related_images_nid = cfaf.nid 
LEFT JOIN files f ON cfaf.field_att_file_fid = f.fid  
JOIN term_node tn2 ON n.nid = tn2.nid 

WHERE n.status = 1 AND n.type = 'article'   AND nr.body LIKE '%kimberly-clark%' AND tn2.tid = 143 ORDER BY n.created DESC LIMIT 3

This returns a recordset that looks like this:

TITLE         |  TEASER           |  NID |  DATE            |  IMAGE                            | IMAGE_ID
==========================================================================================================
Exec Profile  |  Could you please | 67491 | April 29, 2011  | sites/default/files/kcjones.jpg   | 67572
Unilver Ads   |  Unilever topped  | 67421 | April 20, 2011  | sites/default/files/unilever.jpg  | 66889

This is all well and good. But what I need to do is (in the same query, somehow) also return the tid that goes with the image_id from the term_node table. On its own for a single image node that query is simple:

SELECT tid FROM term_node WHERE nid = 67572

But how do I modify the existing query to bring in the tid based on the image_id found in the main part of the query? I'm guessing I need some kind of subquery, but this is stretching the limits of my sql chops...

Answer 1

You already have the {term_node} table joined in your query so I'm pretty sure you should just be able to change your first line to include tn2.tid. Try this:

SELECT DISTINCT n.title, nr.teaser, n.nid, tn2.tid, DATE_FORMAT(FROM_UNIXTIME(n.created), '%M %e, %Y') AS date, f.filepath AS image, cfri.field_related_images_nid as image_id 
FROM node n JOIN node_revisions nr ON n.nid = nr.nid 
LEFT JOIN content_field_related_images cfri ON (n.nid = cfri.nid AND cfri.delta = 0) 
LEFT JOIN content_field_att_file cfaf ON cfri.field_related_images_nid = cfaf.nid 
LEFT JOIN files f ON cfaf.field_att_file_fid = f.fid  
JOIN term_node tn2 ON n.nid = tn2.nid 

WHERE n.status = 1 AND n.type = 'article'   AND nr.body LIKE '%kimberly-clark%' AND     tn2.tid = 143 ORDER BY n.created DESC LIMIT 3