CODEWITHSUNDEEP
mysql
mingfish_004
mingfish_004

Reputation: 1413

how can I get categories that share at least one product with another given category?

how can i get categories that share at least one product with another given category in sql? 

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `p2c`
-- ----------------------------
DROP TABLE IF EXISTS `p2c`;
CREATE TABLE `p2c` (
  `products_id` int(11) NOT NULL default '0',
  `categories_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`products_id`,`categories_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of p2c
-- ----------------------------
INSERT INTO `p2c` VALUES ('1001', '1');
INSERT INTO `p2c` VALUES ('1001', '2');
INSERT INTO `p2c` VALUES ('1002', '1');
INSERT INTO `p2c` VALUES ('1003', '2');
INSERT INTO `p2c` VALUES ('1004', '1');
INSERT INTO `p2c` VALUES ('1004', '2');
INSERT INTO `p2c` VALUES ('1005', '3');
INSERT INTO `p2c` VALUES ('1006', '2');
INSERT INTO `p2c` VALUES ('1006', '3');
INSERT INTO `p2c` VALUES ('1007', '4');

enter image description here

Upvotes: 1

Views: 86

Answers (3)

DrCopyPaste
DrCopyPaste

Reputation: 4117

Something like this would do it

SELECT
    DISTINCT categories_id
  FROM p2c
  WHERE
    categories_id != 1
    AND products_id IN
      (SELECT products_id FROM p2c WHERE categories_id = 1)

try out in this fiddle.

Upvotes: 1

user1509107
user1509107

Reputation: 

 select distinct a.categories_id as categories_associated_with_1
    from p2c as a
    inner join (select products_id
    from p2c
    where categories_id=1) as b
    on a.products_id=b.products_id
where a.categories_id<>1

Upvotes: 3

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60493

something like that :

You make an inner join on same table. The products_id must be identical, and categories_id must be different.

select distinct p2.categories_id
from p2c p1
inner join p2c p2 
    on p1.products_id = p2.products_id and 
       p1.categories_id <> p2.categories_id
where p1.categories_id = 1

SqlFiddle

Upvotes: 2

Related Questions