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...