Hello,
I have 2 tables:
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
CREATE TABLE products_daily_compacted_views (
product INTEGER NOT NULL REFERENCES products,
date DATE NOT NULL DEFAULT ('NOW'::TEXT)::DATE,
count INTEGER NOT NULL
);
The table products has 1785 rows, the table products_daily_compacted_views
has 768 rows with date = current_date;
I want to list all the products and the number of times each product has
been viewed:
SELECT p.id, p.name, COALESCE(v.count, 0) AS views
FROM products p LEFT JOIN products_daily_compacted_views v ON p.id = v.product
WHERE v.date = current_date OR v.date IS NULL ORDER BY views DESC
The problem with this query is that it doesn't return all the products,
instead of 1785 rows, it returns 1077 rows
This modified query seems to be correct, it returns all the products...
SELECT p.id, p.name, COALESCE(v.count, 0) AS views
FROM products p LEFT JOIN products_daily_compacted_views v
ON p.id = v.product AND v.date = current_date
ORDER BY views DESC
Could anybody explain to me why does this happen ?
Thank you.
