EverSQL Database Performance Knowledge Base
In MySQL how to join tables using their names which are based on column values of another table?
Database type:
I've customized OpenCart 3 in a way that customer be able to follow category and manufacturer.
In home page, each customer will see a list of products based on what he/she followed.
To save data size, I've used abbreviation for category and customer as c and m, and that a big problem for me to make a join query.
At the other hand, I would like to load a list product_ids order by date_modified first, then in the time of scrolling down, load the full product information as requested.
UPDATED FIDDLE
SqlFiddle: http://sqlfiddle.com/#!9/831301/2
CREATE TABLE follow (
`customer_id` int(11) NOT NULL,
`item_id` int(11) NOT NULL,
`item_type` varchar(1) NOT NULL,
PRIMARY KEY (`customer_id`,`item_id`,`item_type`)
)
INSERT INTO follow (customer_id, item_id, item_type) VALUES
(1, 1, 'm'),
(1, 2, 'm'),
(1, 3, 'm'),
(1, 1, 'c'),
(1, 2, 'c'),
(1, 3, 'c');
-- `m` stands for `manufacturer`
-- 'c' stands for `category`
CREATE TABLE IF NOT EXISTS `product` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`price` decimal(15,4) NOT NULL DEFAULT '0.0000',
`manufacturer_id` int(11) NOT NULL,
`date_added` datetime NOT NULL,
`date_modified` datetime NOT NULL,
PRIMARY KEY (`product_id`)
)
CREATE TABLE IF NOT EXISTS `product_description` (
`product_id` int(11) NOT NULL,
`language_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`product_id`,`language_id`),
KEY `name` (`name`)
)
CREATE TABLE IF NOT EXISTS `category` (
`category_id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) NOT NULL DEFAULT '0',
`top` tinyint(1) NOT NULL,
PRIMARY KEY (`category_id`,`parent_id`),
KEY `parent_id` (`parent_id`)
)
CREATE TABLE IF NOT EXISTS `manufacturer` (
`manufacturer_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
PRIMARY KEY (`manufacturer_id`)
)
To do this I tried to make it simpler for myself but the result is wrong:
UPDATED QUERY
SELECT DISTINCT p.product_id, p.price, procats.category_id FROM product p
LEFT JOIN
(SELECT DISTINCT pc.product_id, pc.category_id FROM follow f2
LEFT JOIN product_to_category pc on (f2.item_id = pc.category_id)
WHERE f2.item_type = 'c') AS procats ON (procats.product_id = p.product_id)
order by p.price
UPDATE : Result of my query:
product_id price category_id
9 15 (null)
1 15 1
1 15 2
1 15 3
2 15 1
3 15 2
4 15 (null)
5 15 3
6 15 (null)
7 15 (null)
8 15 (null)
In addition I'd highly appreciate if you give me any suggestion about improving the whole structure and correcting possible mistakes.