xHrhww2e

1CREATE TABLE follow (
2    `customer_id` int(11) NOT NULL,
3    `item_id` int(11) NOT NULL,
4    `item_type` varchar(1) NOT NULL,
5    PRIMARY KEY (`customer_id`,`item_id`,`item_type`)
6)
7
8INSERT INTO follow (customer_id, item_id, item_type) VALUES
9(1, 1, 'm'), 
10(1, 2, 'm'),
11(1, 3, 'm'),
12(1, 1, 'c'),
13(1, 2, 'c'),
14(1, 3, 'c');
15
16-- `m` stands for `manufacturer`
17-- 'c' stands for `category`
18
19CREATE TABLE IF NOT EXISTS `product` (
20    `product_id` int(11) NOT NULL AUTO_INCREMENT,
21    `price` decimal(15,4) NOT NULL DEFAULT '0.0000',
22    `manufacturer_id` int(11) NOT NULL,
23    `date_added` datetime NOT NULL,
24    `date_modified` datetime NOT NULL,
25    PRIMARY KEY (`product_id`)
26)
27
28CREATE TABLE IF NOT EXISTS `product_description` (
29    `product_id` int(11) NOT NULL,
30    `language_id` int(11) NOT NULL,
31    `name` varchar(255) NOT NULL,
32    PRIMARY KEY (`product_id`,`language_id`),
33    KEY `name` (`name`)
34)
35
36CREATE TABLE IF NOT EXISTS `category` (
37    `category_id` int(11) NOT NULL AUTO_INCREMENT,
38    `parent_id` int(11) NOT NULL DEFAULT '0',
39    `top` tinyint(1) NOT NULL,
40    PRIMARY KEY (`category_id`,`parent_id`),
41    KEY `parent_id` (`parent_id`)
42)
43
44CREATE TABLE IF NOT EXISTS `manufacturer` (
45    `manufacturer_id` int(11) NOT NULL AUTO_INCREMENT,
46    `name` varchar(64) NOT NULL,
47    PRIMARY KEY (`manufacturer_id`)
48)
49	
50SELECT DISTINCT p.product_id, p.model, procats.category_id FROM product p
51    LEFT JOIN 
52    (SELECT DISTINCT pc.product_id, pc.category_id FROM follow f2 
53        LEFT JOIN product_to_category pc on (f2.item_id = pc.category_id) 
54        WHERE f2.item_type = 'c') AS procats ON (procats.product_id = p.product_id)
55    order by p.model