I had 3 tables which were Category
, Product
and Vendor
. Then I tried to make a consecutive join 3 tables in 1 query to count the product for each category.
If a product or it's vendor is deleted, it will be excluded from the list. Moreover, if there was no product available, I still need to keep the category with the 0 count result. Because all tables are using the soft-delete strategy, I need to filter deleted items from the Query to get a proper count result. Then, the problem start from here.
A quick sample is below
Category
--|----------|----------|
id|name |deleted_at|
--|----------|----------|
1|Food | NULL|
2|Stationery| NULL|
Product
--|-------|---------|------|----------|
id|cate_id|vendor_id|name |deleted_at|
--|-------|---------|------|----------|
1| 1| 1|Bread | NULL|
2| 1| 2|Milk | NULL|
5| 2| 2|Pencil| NULL|
Vendor
--|-------|----------|
id|name |deleted_at|
--|-------|----------|
1|Woolies| NULL|
2|Cole |2020-01-18|
Query1 gave an unexpected result
SELECT
c.id AS cate_id,
c.name AS cate_name,
COUNT(p.id) AS total_product
FROM
category c
LEFT JOIN product p
ON p.cate_id = c.id AND p.deleted_at IS NULL
INNER JOIN vendor v
ON v.id = p.vendor_id AND v.deleted_at IS NULL
GROUP BY c.id;
cate_id|cate_name|total_product|
-------|---------|-------------|
1|Food | 1|
Query2 gave a correct response.
SELECT
c.id AS cate_id,
c.name AS cate_name,
COUNT(p.id) AS total_product
FROM
category c
LEFT JOIN (SELECT p1.* FROM product p1 INNER JOIN vendor v ON v.id = p1.vendor_id AND v.deleted_at IS NULL)
AS p ON p.cate_id = c.id AND p.deleted_at IS NULL
GROUP BY c.id;
cate_id|cate_name |total_product|
-------|----------|-------------|
1|Food | 1|
2|Stationery| 0|
I'm not likely sure but I guess the Query1
did the LEFT JOIN
between Category
& Product
tables first, then did the INNER JOIN
on the JOINED Record & Vendor
, finally leading to an unexpected result. Am I correct?
Is there any way to achieve this purpose without using a sub-query?
p.id
column. – Akina Jun 5 '20 at 4:58ONLY_FULL_GROUP_BY
- which is a real trap for the unwary! – Vérace Jun 5 '20 at 6:58