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

advertisements

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');


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