mysql - 在MySQL中，如何使用基于另一个表的列值的名称来连接表？

Question

我定制了 OpenCart 3，让客户能够遵循类别 和制造商。
在主页中，每个客户都会看到基于他/她关注的产品列表。
为了节省数据大小，我使用了类别和客户的缩写为 c 和 m，这对我进行联接查询来说是一个大问题。
另一方面，我想首先按date_modified加载列表product_ids，然后在向下滚动时，根据请求加载完整的产品信息。

更新的 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`)
)

为了做到这一点，我试图让自己变得更简单，但结果是错误的:

更新的查询

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        

更新:我的查询结果:

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)

此外，如果您给我任何关于改进整个结构和纠正可能的错误的建议，我将不胜感激。

Answer 1

您不再选择两个跟踪表，一个用于制造商，一个用于类别，而是只决定一个，这意味着您的跟踪表指向您称为项目的“事物”，该“事物”可以是制造商或类别以及 DBMS无法看到您实际指向的内容并帮助您进行一致性检查。

现在您需要客户通过制造商或类别关注的产品列表。因此，从 product 中进行选择，并使用 IN 或 EXISTS 将其限制为客户关注的产品:

select *
from product
where manufacturer_id in 
(
  select item_id from follow where item_type = 'm' and customer_id = 1
)
or product_id in 
(
  select product_id from product_to_category where category_id in
    (select item_id from follow where item_type = 'c' and customer_id = 1)
)
order by date_modified desc;

SQL fiddle :http://sqlfiddle.com/#!9/831301/11

如果您想要具有类别的产品:

select *
from product p
join product_to_category pc on pc.product_id = p.product_id
join category c on c.category_id = pc.category_id
where p.manufacturer_id in 
(
  select item_id from follow where item_type = 'm' and customer_id = 1
)
or c.category_id in 
(
  select item_id from follow where item_type = 'c' and customer_id = 1
)
order by date_modified desc;

SQL fiddle :http://sqlfiddle.com/#!9/831301/17