我有2个表:goods和cats_goods(关系表).
商品:身份证,姓名.
Cats_goods:good_id,cat_id.
如何只选择同时具有cat_id = 4和cat_id = 24的商品?
试过:
SELECT DISTINCT *
FROM `goods` AS `g`
JOIN `cats_goods` AS `cg` ON (`g`.`id` = `cg`.`good_id`)
WHERE (`cg`.`cat_id` = 24 AND `cg`.`cat_id` = 4)
查询给出0个结果!
UPD:
对于每种商品,cats_goods中有1行,cat_id = 4,cats_goods中有1行,cat_id =24.我只需要选择两个条件都匹配的商品.
UPD2:
商品表结构:
CREATE TABLE IF NOT EXISTS `goods` (
`id` int(11) NOT NULL auto_increment,
`code` varchar(50) NOT NULL default '',
`title` varchar(255) NOT NULL default '',
`price` decimal(10,2) NOT NULL default '0.00',
`file` varchar(50) NOT NULL default '',
`preview` varchar(50) NOT NULL default '',
`order` int(11) NOT NULL default '0',
`selltype_id` int(11) NOT NULL default '0',
`xml_date` varchar(50) NOT NULL default '',
`invalid` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `order` (`order`),
KEY `selltype_id` (`selltype_id`),
KEY `code` (`code`),
KEY `invalid` (`invalid`),
KEY `xml_date` (`xml_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5014 ;
货物表数据:
(4964, '00000001731', 'gold 585', 10000.00, '', '', 0, 2, '', 0),
(4965, '00000001733', 'gold 585', 10000.00, '', '', 0, 2, '', 0),
(4966, '00000001769', 'gold 585', 8000.00, '', '', 0, 2, '', 0),
(4967, '00000001767', 'gold 585', 8000.00, '', '', 0, 2, '', 0),
cats_goods表结构:
CREATE TABLE IF NOT EXISTS `cats_goods` (
`id` int(10) unsigned NOT NULL auto_increment,
`good_id` int(10) unsigned NOT NULL default '0',
`cat_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `good_id` (`good_id`,`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=37530 ;
cats_goods表数据:
(37474, 4964, 24),
(37478, 4966, 24),
(37477, 4966, 4),
(37476, 4965, 24),
(37475, 4965, 4),
(37475, 4967, 4),
仅必须选择商品4965和4966.