clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2042890 fiddles created (16635 in the last week).

select version();
version()
8.0.20
 hidden batch(es)


CREATE TABLE category ( id INTEGER, name VARCHAR (25), deleted_at DATE ); INSERT INTO category VALUES (1, 'Food', NULL), (2, 'Stationery', NULL); SELECT * FROM category;
id name deleted_at
1 Food
2 Stationery
 hidden batch(es)


CREATE TABLE product ( id INTEGER, cate_id INTEGER, vendor_id INTEGER, name VARCHAR (25), deleted_at DATE ); INSERT INTO product VALUES (1, 1, 1, 'Bread', NULL), (2, 1, 2, 'Milk', NULL), (5, 2, 2, 'Pencil', NULL); SELECT * FROM product;
id cate_id vendor_id name deleted_at
1 1 1 Bread
2 1 2 Milk
5 2 2 Pencil
 hidden batch(es)


CREATE TABLE vendor ( id INTEGER, name VARCHAR (25), deleted_at DATE ); INSERT INTO vendor VALUES (1, 'Woolies', NULL), (2, 'Cole', '2020-01-18'); SELECT * FROM vendor;
id name deleted_at
1 Woolies
2 Cole 2020-01-18
 hidden batch(es)


SELECT c.id, c.name, COUNT(1) FROM category c LEFT JOIN product p ON c.id = p.cate_id AND c.deleted_at IS NOT NULL JOIN vendor v ON p.vendor_id = v.id AND v.deleted_at IS NOT NULL GROUP BY c.id, c.name;
id name COUNT(1)
 hidden batch(es)


SELECT p.id AS product_id, p.name AS product_name, COUNT(p.id) AS product_count FROM product p JOIN category c ON p.cate_id = c.id AND c.deleted_at IS NULL JOIN vendor v ON p.vendor_id = v.id AND v.deleted_at IS NULL WHERE p.deleted_at IS NULL GROUP BY p.id, p.name;
product_id product_name product_count
1 Bread 1
 hidden batch(es)


SELECT c.id AS cate_id, c.name AS cate_name, COUNT(p.id) AS total_product, p.id AS product_id 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;
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_513414584.c.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
 hidden batch(es)


SELECT c.id AS cate_id, c.name AS cate_name, COUNT(p.id) AS total_product, p.id AS product_id 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;
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_513414584.c.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
 hidden batch(es)


SELECT DISTINCT c.id AS cate_id, c.name AS cate_name, COUNT(p.id) AS total_product -- p.id AS product_id FROM category c LEFT JOIN product p ON p.cate_id = c.id AND p.deleted_at IS NOT NULL LEFT JOIN vendor v ON v.id = p.vendor_id AND v.deleted_at IS NOT NULL GROUP BY c.id, c.name, p.id;
cate_id cate_name total_product
1 Food 0
2 Stationery 0
 hidden batch(es)


SELECT c.id AS cate_id, c.name AS cate_name, COUNT(p.id) AS total_product -- p.id AS product_id 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, c.name, p.id;
cate_id cate_name total_product
1 Food 1
2 Stationery 0
 hidden batch(es)


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|
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Category --|----------|----------| id|name |deleted_at| --|----------|-----' at line 1
 hidden batch(es)