Product Database - MySQL query with multiple JOINs

Question

First, my table structure:

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`)

I need one SQL query that captures all products by some criteria and adds the total stock and total amount sold from the table STOCK, brand name from the table BRAND, child and parent categories from CATEGORY.

This was my attempt, which does not work:

"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"

Initially, I had a series of requests - first, get all the products, then grab categories, then brands, then stocks / sell. I am just wondering if I can do all this in one query?

I am new to SQL.

Answer 1

Thanks to @Shadow, this is the request I need, which works fine:

    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