CODEWITHSUNDEEP
mysqlsqljoin
s.webbandit
s.webbandit

Reputation: 17028

Select rows with JOIN which have 2 conditions on second table

I have 2 tables: goods and cats_goods (relations table).

Goods: id, name.

Cats_goods: good_id, cat_id.

How to select ONLY goods which have BOTH cat_id=4 AND cat_id=24?

Have tried:

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)

Query gives 0 results!

UPD: For each good there is 1 row in cats_goods with cat_id = 4 and one row in cats_goods with cat_id = 24. I only need to select those goods which have BOTH conditions match.

UPD2:

goods table structure:

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 ;

goods table data:

(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 table structure:

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 table data:

(37474, 4964, 24),
(37478, 4966, 24),
(37477, 4966, 4),
(37476, 4965, 24),
(37475, 4965, 4),
(37475, 4967, 4),

ONLY goods 4965 and 4966 must be selected.

Upvotes: 3

Views: 137

Answers (4)

Robert
Robert

Reputation: 25763

Try this:

SELECT DISTINCT *
FROM `goods` AS `g`
JOIN `cats_goods` AS `cg` ON (`g`.`id` = `cg`.`good_id`) 
                          and (`cg`.`cat_id` = 24 OR `cg`.`cat_id` = 4)

or

SELECT DISTINCT *
FROM `goods` AS `g`
JOIN `cats_goods` AS `cg` ON (`g`.`id` = `cg`.`good_id`) 
where `g`.`id` in (24,4)

or

  SELECT DISTINCT *
    FROM `goods` AS `g`
    JOIN `cats_goods` AS `cg1` ON (`g`.`id` = `cg1`.`good_id`) 
                               and (`cg1`.`cat_id` = 24) 
    JOIN `cats_goods` AS `cg2` ON (`g`.`id` = `cg2`.`good_id`) 
                               and (`cg2`.`cat_id` = 4)

Upvotes: 2

Nalaka526
Nalaka526

Reputation: 11474

Try

SELECT DISTINCT *
FROM `goods` AS `g`
JOIN `cats_goods` AS `cg1` ON (`g`.`id` = `cg1`.`good_id`)
JOIN `cats_goods` AS `cg2` ON (`g`.`id` = `cg2`.`good_id`)
WHERE (`cg1`.`cat_id` = 24 AND `cg2`.`cat_id` = 4)

Upvotes: 2

Praveen Kumar Purushothaman
Praveen Kumar Purushothaman

Reputation: 167250

This query is rather funny. How can one field, have two values at the same time?

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)

Change it to OR in the WHERE condition, buddy.

SELECT DISTINCT *
FROM `goods` AS `g`
JOIN `cats_goods` AS `cg` ON (`g`.`id` = `cg`.`good_id`)
WHERE (`cg`.`cat_id` = 24 OR `cg`.`cat_id` = 4)

If what I asked is possible, can you show the table dump?

Upvotes: 0

Zo Has
Zo Has

Reputation: 13038

SELECT a.good_id, a.cat_id from cats_goods a inner join goods b on a.good_id=b.id where a.cat_id in (4,24);

Upvotes: 0

Related Questions