mysql - 产品数据库 - 具有多个 JOIN 的 MySQL 查询

Question

首先是我的表结构:

Products Table:

 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `price` decimal(10,2) NOT NULL,
 `list_price` decimal(10,2) NOT NULL,
 `brand` int(11) NOT NULL,
 `category` int(11) NOT NULL,
 `image` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `description` text COLLATE utf8_unicode_ci NOT NULL,
 `featured` tinyint(4) NOT NULL DEFAULT '0',
 `deleted` tinyint(4) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)

Categories Table:

 `id` int(11) NOT NULL AUTO_INCREMENT,
 `category` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `parent` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)

Brand Table:

 `id` int(11) NOT NULL AUTO_INCREMENT,
 `brand` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)

Stock Table:

 `id` int(11) NOT NULL AUTO_INCREMENT,
 `product_id` int(11) NOT NULL,
 `size` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
 `stock` int(11) NOT NULL,
 `sold` int(11) NOT NULL,
 `reserved` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `product_sizes` (`product_id`,`size`),
 KEY `product_id` (`product_id`),
 CONSTRAINT `stock_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)

我想要一个 SQL 查询，它可以按任何标准获取所有产品，并添加 STOCK 表中的总库存和总销售量，BRAND 中的品牌名称> 表，CATEGORY 表中的子类别和父类别。

这是我的尝试，但行不通:

"SELECT
    a.title,
    COALESCE(SUM(e.stock),0),
    COALESCE(SUM(e.sold),0),
    a.price,
    c.category AS 'parent',
    d.category AS 'child',
    b.brand,
    a.featured
FROM
    products a
        JOIN brand b
            ON a.brand = b.id
        JOIN categories c
            ON a.category = c.id
        LEFT JOIN categories d
            ON c.parent = d.id
        JOIN stock e
            ON a.id = e.product_id

WHERE a.deleted = 0 ORDER BY a.title ASC"

最初我有一系列查询 - 首先获取所有产品，然后获取类别，然后是品牌，然后是库存/已售。我只是想知道是否可以在一个查询中完成所有操作？

我对 SQL 很陌生。

Answer 1

感谢@Shadow，这是我需要的查询，效果很好:

    SELECT
    a.id,
    a.title,
    COALESCE(SUM(e.stock),0) AS 'stock',
    COALESCE(SUM(e.sold),0) AS 'sold',
    a.price,
    c.category AS 'child_category',
    d.category AS 'parent_category',
    b.brand,
    a.featured
FROM
    products a
        JOIN brand b
            ON a.brand = b.id
        JOIN categories c
            ON a.category = c.id
        LEFT JOIN categories d
            ON c.parent = d.id
        JOIN stock e
            ON a.id = e.product_id

WHERE a.deleted = 0
GROUP BY a.id, a.title, a.price, c.category, d.category, b.brand, a.featured ORDER BY a.title ASC