查询帮助;我需要一个子查询吗？

Question

（已贴在drupal.stackexchange.com没有响应;因为这个问题是MySQL特有希望这里有人能帮助... ...）

我继承了这个代码，在我们的主页上运行。它用于自定义显示特定的相关信息。以下是如何设置：

我们有一篇文章。它有一系列节点引用字段，允许我们链接相关图像（实际上是节点本身，其中包含爆头和一些文本;它不是实际附加的图像）。每个爆头都有通过ImageCache创建的缩略图，以便我们可以在搜索结果和列表类型页面上显示一个小版本。

我们有一个新的要求，即如果特定文章的相关图像节点具有与其相关的特定分类术语（tid），则它应该以与其他图像不同的方式显示。

现有的查询（我没写）看起来是这样的：

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 

这返回记录，看起来像这样：

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 

这是一切都很好。但是我需要做的是（在同一个查询中，也以某种方式）也返回来自term_node表的image_id的tid。就其本身的查询是简单的单个图像节点：

SELECT tid FROM term_node WHERE nid = 67572 

但是我怎么修改现有的查询基于查询的主要部分发现image_id在TID带来了什么？我猜我需要某种子查询，但这是拉伸我的sql排印的极限...

--------------------- -------------------------------------------------- -------------------

UPDATE每请求时，这里是在查询中使用的所有表的表结构：

-- Table structure for table `content_field_att_file` 
-- 

CREATE TABLE IF NOT EXISTS `content_field_att_file` (
    `vid` int(10) unsigned NOT NULL DEFAULT '0', 
    `nid` int(10) unsigned NOT NULL DEFAULT '0', 
    `delta` int(10) unsigned NOT NULL DEFAULT '0', 
    `field_att_file_fid` int(11) DEFAULT NULL, 
    `field_att_file_list` tinyint(4) DEFAULT NULL, 
    `field_att_file_data` text, 
    PRIMARY KEY (`vid`,`delta`), 
    KEY `nid` (`nid`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

-- -------------------------------------------------------- 

-- 
-- Table structure for table `content_field_related_images` 
-- 

CREATE TABLE IF NOT EXISTS `content_field_related_images` (
    `vid` int(10) unsigned NOT NULL DEFAULT '0', 
    `nid` int(10) unsigned NOT NULL DEFAULT '0', 
    `delta` int(10) unsigned NOT NULL DEFAULT '0', 
    `field_related_images_nid` int(10) unsigned DEFAULT NULL, 
    PRIMARY KEY (`vid`,`delta`), 
    KEY `nid` (`nid`), 
    KEY `field_related_images_nid` (`field_related_images_nid`), 
    KEY `delta` (`delta`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

-- -------------------------------------------------------- 

-- 
-- Table structure for table `files` 
-- 

CREATE TABLE IF NOT EXISTS `files` (
    `fid` int(10) unsigned NOT NULL AUTO_INCREMENT, 
    `uid` int(10) unsigned NOT NULL DEFAULT '0', 
    `filename` varchar(255) NOT NULL DEFAULT '', 
    `filepath` varchar(255) NOT NULL DEFAULT '', 
    `filemime` varchar(255) NOT NULL DEFAULT '', 
    `filesize` int(10) unsigned NOT NULL DEFAULT '0', 
    `status` int(11) NOT NULL DEFAULT '0', 
    `timestamp` int(10) unsigned NOT NULL DEFAULT '0', 
    PRIMARY KEY (`fid`), 
    KEY `uid` (`uid`), 
    KEY `status` (`status`), 
    KEY `timestamp` (`timestamp`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=100810 ; 

-- -------------------------------------------------------- 

-- 
-- Table structure for table `node` 
-- 

CREATE TABLE IF NOT EXISTS `node` (
    `nid` int(10) unsigned NOT NULL AUTO_INCREMENT, 
    `vid` int(10) unsigned NOT NULL DEFAULT '0', 
    `type` varchar(32) NOT NULL DEFAULT '', 
    `language` varchar(12) NOT NULL DEFAULT '', 
    `title` varchar(255) NOT NULL DEFAULT '', 
    `uid` int(11) NOT NULL DEFAULT '0', 
    `status` int(11) NOT NULL DEFAULT '1', 
    `created` int(11) NOT NULL DEFAULT '0', 
    `changed` int(11) NOT NULL DEFAULT '0', 
    `comment` int(11) NOT NULL DEFAULT '0', 
    `promote` int(11) NOT NULL DEFAULT '0', 
    `moderate` int(11) NOT NULL DEFAULT '0', 
    `sticky` int(11) NOT NULL DEFAULT '0', 
    `tnid` int(10) unsigned NOT NULL DEFAULT '0', 
    `translate` int(11) NOT NULL DEFAULT '0', 
    PRIMARY KEY (`nid`), 
    UNIQUE KEY `vid` (`vid`), 
    KEY `node_changed` (`changed`), 
    KEY `node_created` (`created`), 
    KEY `node_moderate` (`moderate`), 
    KEY `node_promote_status` (`promote`,`status`), 
    KEY `node_status_type` (`status`,`type`,`nid`), 
    KEY `node_title_type` (`title`,`type`(4)), 
    KEY `node_type` (`type`(4)), 
    KEY `uid` (`uid`), 
    KEY `tnid` (`tnid`), 
    KEY `translate` (`translate`), 
    KEY `status` (`status`,`type`,`created`), 
    KEY `status_2` (`status`,`type`,`created`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=102682 ; 

-- -------------------------------------------------------- 

-- 
-- Table structure for table `node_revisions` 
-- 

CREATE TABLE IF NOT EXISTS `node_revisions` (
    `nid` int(10) unsigned NOT NULL DEFAULT '0', 
    `vid` int(10) unsigned NOT NULL AUTO_INCREMENT, 
    `uid` int(11) NOT NULL DEFAULT '0', 
    `title` varchar(255) NOT NULL DEFAULT '', 
    `body` longtext NOT NULL, 
    `teaser` longtext NOT NULL, 
    `log` longtext NOT NULL, 
    `timestamp` int(11) NOT NULL DEFAULT '0', 
    `format` int(11) NOT NULL DEFAULT '0', 
    PRIMARY KEY (`vid`), 
    KEY `nid` (`nid`), 
    KEY `uid` (`uid`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=102690 ; 

-- -------------------------------------------------------- 

-- 
-- Table structure for table `term_node` 
-- 

CREATE TABLE IF NOT EXISTS `term_node` (
    `nid` int(10) unsigned NOT NULL DEFAULT '0', 
    `vid` int(10) unsigned NOT NULL DEFAULT '0', 
    `tid` int(10) unsigned NOT NULL DEFAULT '0', 
    PRIMARY KEY (`tid`,`vid`), 
    KEY `vid` (`vid`), 
    KEY `nid` (`nid`), 
    KEY `tid` (`tid`,`nid`), 
    KEY `tid_2` (`tid`,`nid`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

Answer 1

你可以为term_node表添加第二个联接并将该列添加到选择列表中：

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, tn_img.tid as image_tid 

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 
LEFT JOIN term_node tn_img ON cfri.field_related_images_nid = tn_img.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 

这是重要的一行：LEFT JOIN term_node tn_img on cfri.field_related_images_nid = tn_img.nid，不要忘记将列添加到选择列表！

这可能是我的查询是错的，我们没有对表结构多的信息，如果没有工作，发布的数据库模式:)

更新，以反映意见讨论

Answer 2

我可能失去了一些东西，但这应该工作

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 
JOIN term_node tn tn2 ON n.nid = tn2.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 

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