Question

This one has been haunting me for quite a while now.. I have been developing my own CMS using a MySQL database; each uploaded image is assigned to a category, according to which part of the site it is related to (I need to do this since each category has its own way to handle images).

我有几个用于各种实体的表，一个图像表和一个关联表：images_assoc，它们的基本结构如下：

CREATE TABLE `images` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default   ,
  `link` varchar(255) NOT NULL default   ,
  `idcategory` int(11) NOT NULL default  0 ,
  PRIMARY KEY  (`id`),
  KEY `idcategory` (`idcategory`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
INSERT INTO `images` (`id`, `name`, `link`, `idcategory`) VALUES (1,  some name ,  foo.jpg , 1);
CREATE TABLE `images_assoc` (
  `id` int(11) NOT NULL auto_increment,
  `idimage` int(11) NOT NULL default  0 ,
  `idelement` int(11) NOT NULL default  0 ,
  PRIMARY KEY  (`id`),
  KEY `idimage` (`idimage`,`idelement`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
INSERT INTO `images_assoc` (`id`, `idimage`, `idelement`) VALUES (1, 1, 2);
CREATE TABLE v`some_entity` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(250) NOT NULL,
  `description` text NOT NULL,
  -- some other data
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

在网站的各个页面中，我需要做的是检索页面元素列表及其相关图像。我还没能用一个选择来做到这一点。我现在所做的是为页面元素运行select，然后为每个元素运行查询以检索任何关联的图像，查询如下：

SELECT i.id, i.link, i.name
FROM images_assoc AS ia, images AS i
WHERE ia.idelement =  1 
AND i.idcategory =  1 
AND i.id = ia.idimage

我最初提出的一个解决方案是：

SELECT t. * , i.id, i.link, i.name
FROM (
 (
  (
   contents AS t
  )
  LEFT JOIN images_assoc AS ia ON t.id = ia.idelement
 )
 LEFT JOIN images AS i ON i.id = ia.idimage
)
WHERE i.idcategory =  1 
AND i.id = ia.idimage

but it left out any element with no associated image, which is the exact contrary of the purpose of the left join. Later, I tried changing the query to this:

SELECT t. * , i.id, i.link, i.name
FROM (
 (
  (
   contents AS t
  )
  LEFT JOIN images_assoc AS ia ON t.id = ia.idelement
 )
 LEFT JOIN images AS i ON ( i.id = ia.idimage
 AND i.idcategoriy =  1  )
)

但是，查询仍然是错误的：我最终得到了一个类似交叉连接的结果，因为类别限制稍后会应用。。

Does anyone have any suggestions? Any tips regarding the database structure are welcome as well..

Answer 1

好吧，你的idcategory条件永远不会匹配，除非在另一个表中有相应的对应项，这就是为什么你没有相应图像的结果“消失”了，左联接表现正确。

试试这个：

sql查询：

SELECT some_entity.title, some_entity.description, some_entity.id as entityid, images.id as imageid, images.link, images.name
FROM 
some_entity 
LEFT JOIN images_assoc ON (some_entity.id = images_assoc.idelement)
LEFT JOIN (SELECT * FROM images WHERE idcategory=1) images ON (images.id = images_assoc.idimage)

或者可能更好（obe只是为了说明它为什么不起作用）：

SELECT some_entity.title, some_entity.description, some_entity.id AS entityid, images.id AS imageid, images.link, images.name
FROM some_entity
LEFT JOIN images_assoc ON ( some_entity.id = images_assoc.idelement ) 
LEFT JOIN images ON ( images.id = images_assoc.idimage ) 
WHERE images.idcategory =   1  OR images.idcategory IS NULL

测试数据：

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
CREATE TABLE IF NOT EXISTS `images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT   ,
  `link` varchar(255) NOT NULL DEFAULT   ,
  `idcategory` int(11) NOT NULL DEFAULT  0 ,
  PRIMARY KEY (`id`),
  KEY `idcategory` (`idcategory`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=124 ;
CREATE TABLE IF NOT EXISTS `some_entity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(250) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=322 ;
CREATE TABLE IF NOT EXISTS `images_assoc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idimage` int(11) NOT NULL DEFAULT  0 ,
  `idelement` int(11) NOT NULL DEFAULT  0 ,
  PRIMARY KEY (`id`),
  KEY `idimage` (`idimage`,`idelement`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `images` (`id`, `name`, `link`, `idcategory`) VALUES
(123,  some name ,  foo.jpg , 1);
INSERT INTO `images_assoc` (`id`, `idimage`, `idelement`) VALUES
(1, 123, 321);
INSERT INTO `some_entity` (`id`, `title`, `description`) VALUES
(321,  test ,  test );

友情链接