A small question for SQL

0 like 0 dislike
22 views
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);
products (id,name,id_company,data)


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.
by | 22 views

7 Answers

0 like 0 dislike
So will work:
\r
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 
by
0 like 0 dislike
If I understand correctly, just add a WHERE clause:
\r
\rOR p.data IS NULL
by
0 like 0 dislike
where (to_days(now() — to_days(p.data)) <= 10 OR (to_days(now() — to_days(p.data)) IS NULL
by
0 like 0 dislike
But it does not work?
\r
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 
by
0 like 0 dislike
It would be nice to specify a DBMS, in principle, with respect to the universal should be something like this:
\r
\r
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 

\r
Ie in fact here is run two queries with the opposite conditions. If MSSQL — it would be possible to do a CROSS APPLY
by
0 like 0 dislike
there are two appropriate ways-one through a complex query
\r
the second using 2 relatively simple query
\r
first, pull out the data for the products table
from these data, a selectable list eCompany using php(or whatever program)
\r
this list with one query get the names of the companies
\r
then generated from these data the desired output.
by
0 like 0 dislike
Answers from VolCh, xdenser ArtemS and it seems fine, thank you. But the performance issue remains, probably will have to do as planned at first...
by

Related questions

0 like 0 dislike
3 answers
asked Mar 25, 2019 by Jakeroid
0 like 0 dislike
3 answers
asked Mar 24, 2019 by Amka
0 like 0 dislike
2 answers
asked Mar 22, 2019 by pdx
0 like 0 dislike
4 answers
110,608 questions
257,186 answers
0 comments
28,716 users