mysql - 查询帮助；我需要一个子查询吗？

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，不要忘记将该列也添加到选择列表中!

可能我的查询有误，我们没有太多关于表结构的信息，如果它不起作用，请发布数据库模式:)

已更新以反射(reflect)评论中的讨论