A small SQL question

Question

SQL

Welcome all! There is a small question for those who understand SQL:

Suppose we have companies and products in our database. There are many companies, even more products.
Each product is accompanied by the date of its entry into the database, and the products are "perishable", i.e. after N days the product is no longer relevant.

You need to get a type plate (company name, number of current products).

So, let's imagine that we have the following tables: First, everything worked through two queries: one took each company in turn, the other went through the product table, finding relevant ones and counting them. As you know, executing a query in a loop is the worst of evils, and besides, both tables turned out to be very, very large, so this solution is not suitable for performance reasons.

companies (id, name);
products (id,name,id_company,data)



To improve performance, I tried to use a left outer join of tables in
order to have in the output both those companies that have goods and those that do not.

Approximately we get the following request: The request works quickly, but incorrectly: due to the condition, it does not display those companies that have no products at all. Further, due to fatigue and limited knowledge of SQL, the head does not think yet, I appeal to your help ... Is it really possible to get out in such a situation with one request? In principle, if it is not possible to do it simply and efficiently enough, there is an idea to create a table in memory that stores the number of actual products for each company and update this table on major holidays such as the arrival of new products and according to the schedule every day.

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


Answer 1

This is how it will work:

select c.name, count( p.id ) as cnt
from company c
left join products p on c.id = p.id_company and  to_days(now()) - to_days(p.data) <= 10 
group by c.id

Answer 2

If I understand correctly, just add to WHERE:
OR p.data IS NULL

Answer 3

where (to_days(now() - to_days(p.data)) <= 10 OR (to_days(now() - to_days(p.data)) IS NULL

Answer 4

And that doesn't work?

select c.name, 
         (select count(*)  
          from products p
          where p.id_company = c.id
          and to_days(now()) - to_days(p.data) <= 10) as cnt
from company c 

Answer 5

It would be nice to specify the DBMS, in principle, something like this should be relatively universal:

select r.name,SUM(r.cnt) from
(
  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.name

  union

  select c.name, 0 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.name
) as r

group by r.name

Those. in fact, two requests with opposite conditions are executed here. If MSSQL - it would be possible to do CROSS APPLY

Answer 6

there are two acceptable options, one through a complex query , the
second through 2 relatively simple queries
, first we pull out the data for the product table
from this data, select the list of company_ids through php (or whatever you program)
using this list, with one request, we get the names of the companies
, then we form the required output from this data.

Answer 7

Answers from VolCh, xdenser and ArtemS seem to fit, thanks. But the problem with performance remains, apparently it will have to be done as planned at the beginning ...

Answer 8

This is how it is possible, most likely it will work faster than through the usual left join of the entire table:
select c.name, count( p.id ) as cnt
from company c
left join (select id, id_company from products where to_days(now()) — to_days(p.data) <= 10) as p on c.id = p.id_company
group by c.id