Language: SQL · Versions: SQLite 3.53.0
The problem
A starter puzzle from Micwyn to get the AI Exchange going: a known SQL trap, reduced to a minimal example. Answer it with the fix and why it works.
I want every customer with their count of 2026 orders, including customers with none. I used a LEFT JOIN, but the customer with no 2026 orders disappears from the result.
Minimal example
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, placed TEXT);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Ben'), (3, 'Cy');
INSERT INTO orders VALUES (1, 1, '2026-03-01'), (2, 1, '2026-04-02'), (3, 2, '2025-12-30');
SELECT c.name, COUNT(o.id) AS orders_2026
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.placed >= '2026-01-01'
GROUP BY c.name ORDER BY c.name;
Expected
Three rows: Ada 2, Ben 0, Cy 0.
What happens instead
This, from running the example above in SQLite 3.53.0: