1

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?

5
  • Without enabled ONLY_FULL_GROUP_BY the result of such query is NOT deterministic. When the column/expression is listed in SELECT without aggregate ffunction, and it is absent in grouping expression, then its value will be taken from ANY row in a group. In shown query it is p.id column. – Akina Jun 5 '20 at 4:58
  • Inner join output only those records which have the records matched joinjing conditions in all source tables wheneas left join needs matching in left table only. – Akina Jun 5 '20 at 5:03
  • @Akina Thank you, I already updated my question so that it wouldn't cause the problem of the sql_mode= ONLY_FULL_GROUP_BY. Actually, my intention of the SELECT p.id is only demonstration which I think will make the example easy to understand, but it might unnecessary. – Tuan Vu Jun 5 '20 at 6:28
  • Take a look at this fiddle and switch between MySQL 8.0 and 5.6 - see the difference in your queries - it demonstrates @Akina 's point about ONLY_FULL_GROUP_BY - which is a real trap for the unwary! – Vérace Jun 5 '20 at 6:58
  • @Vérace Thanks for your detail explanation. I already updated the question. – Tuan Vu Jun 5 '20 at 7:17
1

Use

SELECT c.id AS cate_id, c.name AS cate_name, COUNT(p.id) AS total_product
FROM Category c
LEFT JOIN ( Product p 
            INNER JOIN Vendor v ON v.id = p.vendor_id AND v.deleted_at IS NULL 
            ) ON p.cate_id = c.id AND p.deleted_at IS NULL
GROUP BY c.id, c.name;

or

SELECT c.id AS cate_id, c.name AS cate_name, COUNT(p.id) AS total_product
FROM Product p 
INNER JOIN Vendor v ON v.id = p.vendor_id AND v.deleted_at IS NULL 
RIGHT JOIN Category c ON p.cate_id = c.id AND p.deleted_at IS NULL
GROUP BY c.id, c.name;

fiddle


I guess the Query1 did the LEFT JOIN between Category & Product first, then did the INNER JOIN on the JOINED Record & Vendor, finally leading to an unexpected result. Am I correct?

Yes, if at least one outer joining is present then tables scanning is performed in query text ordering (like STRAIGHT_JOIN is used).


It would be really thankful if you can suggest a name or a link so that I can do some research about it.

MySQL 8.0 Reference Manual / ... / JOIN Clause

2
  • Thanks for your impressive answer. Specific to your Query1, this is the first time I see a LEFT JOIN technique like this. It would be really thankful if you can suggest a name or a link so that I can do some research about it. – Tuan Vu Jun 5 '20 at 7:14
  • @TuanVu Link added. – Akina Jun 5 '20 at 7:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.