当数据库中只有一个条目时,SQL查询返回重复项

提问

我正在尝试使用测试数据库来学习SQL(MySQLi PHP)

我从两个表add_images和item提取数据,其中“ item”的主键被引用为“ add_images”中的外键.一个“项目”将只有一个图像(add_images).

我需要获取有关由它的image_name引用的项目的特定详细信息.

我在d7.jpg下的数据库中只有一个条目,其类型为VARCHAR.当我运行以下查询时,我得到34个结果,我应该只得到一个？为什么是这样？

当我在phpmyadmin中运行查询时,也会发生这种情况

这是查询

SELECT item_name, catagory, brand, store, location, month, year, details FROM add_images, item WHERE add_images.image_name='d7.jpg '

我认为我在配置表的方式上根本做错了什么？

希望有人可以指教！

谢谢

下面是两个表

CREATE TABLE IF NOT EXISTS `add_images`( `image_id` int(10) unsigned NOT NULL     AUTO_INCREMENT COMMENT 'unique id for an image',
  `item_id` int(10) unsigned NOT NULL COMMENT 'unique id for the item been added',
  `image_name` varchar(20) NOT NULL COMMENT 'name of the image',
  `type` enum('standard','deleted','profile','look','item') NOT NULL COMMENT 'status and       type of image',
  `date_added` varchar(50) NOT NULL COMMENT 'date image was added',
  PRIMARY KEY (`image_id`),
  UNIQUE KEY `item_id` (`item_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='Add images to item or profile picture'     AUTO_INCREMENT=50 ;


CREATE TABLE IF NOT EXISTS `item` (
 `item_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key for item',
 `item_name` varchar(20) NOT NULL COMMENT 'title name of the item',
 `catagory` enum('accessories','jackets','coats','footwear','legwear','jeanswear','dresses','shirts','tops','t-shirts','knitwear','skirts','shorts') NOT NULL COMMENT 'item catagory',
 `brand` varchar(20) NOT NULL COMMENT 'brand of product',
 `store` varchar(20) NOT NULL COMMENT 'store the item was purchased',
 `location` varchar(20) NOT NULL COMMENT 'location the item was purchased',
 `month` enum('January','February','March','April','May','June','July','August','September','October','November','December') NOT NULL COMMENT 'month the item was purchased',
 `year` int(2) NOT NULL COMMENT 'year the item was purchased',
 `details` varchar(500) NOT NULL COMMENT 'details about the item description',
 `date` varchar(50) NOT NULL COMMENT 'date item created',
 PRIMARY KEY (`item_id`),
 UNIQUE KEY `item_id` (`item_id`)
)    ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='holds data about the item'     AUTO_INCREMENT=72 ;

最佳答案

您的查询会进行联接,但不会在item表的WHERE子句中放置任何条件,因此无论item_id是否匹配,查询都会将所有item条目与add_images结果合并.您将需要更改查询以反映这一点；

SELECT item_name, catagory, brand, store, location, month, year, details 
FROM add_images, item 
WHERE add_images.image_name='d7.jpg '
  AND item.item_id = add_images.item_id;

编辑：如果将查询重写为显式联接,则更容易查看是否缺少表之间的链接；

SELECT item_name, catagory, brand, store, location, month, year, details 
FROM add_images
JOIN item ON item.item_id = add_images.item_id   -- ON specifies the link
WHERE add_images.image_name='d7.jpg ';