Greetings to all! There is a small question for understanding SQL:
Suppose we have in the database of the company and products. Many companies, products even more.
Each product is accompanied by the date of its registration in the database, and the products are "perishable", i.e., after N days the product is already irrelevant.
Required to sign the form (company name, number of actual products).
So, suppose we have the following signs:
companies (id, name);
At first, everything worked using two queries, one took each one in turn company, and the other passed on the products table, finding the relevant and counting.
As you know, execution of a query in a loop is the worst of evils, besides, both plates were very large, so this solution is not suitable for performance reasons.
To improve performance I tried to use a left outer join of tables
in order to have the output, and those companies who have goods and those who do not have them.
Get approximately such request:
select c.name, count( p.id ) as cnt
from company c left join products p on c.id = p.id_company
where to_days(now()) - to_days(p.data) <= 10
group by c.id
The query runs fast but incorrect: because the conditions are not displays those companies who have no products at all.
Further, because of the fatigue and limited knowledge of SQL, the head does not think, is crying out for your help...
Is it possible at all to get out in such a situation a single query?
In principle, if you fail to make a quite simple and efficient, is the idea to create a table in memory that stores for each company the number of current products and update this table on major holidays type of new products and on schedule every day.