2
\$\begingroup\$

I have two table. I want count how many times a value is in other table. So the codice from table "sorgente" is different in table contatore, because I have the suffix 'BRANO-' before the code.

sorgente
| codice | nome  |
|   15   | mario |
|   16   | mary  |

contatore
| nome_evento |   data     |
| BRANO-15    | 2020-08-15 |
| BRANO-15    | 2020-08-16 |
| BRANO-16    | 2020-08-14 |

the query for select is

select
    s.codice,
    (
        select count(*) 
        from contatore c 
        where c.nome_evento = concat('BRANO-', s.codice) 
    ) no_matches_in_contatore
from sorgente s

but the original "sorgente" table has 25000 records, the "contatore" table has at now 60000 records. when I try to execute the query it takes 600 seconds.... can you help me?

\$\endgroup\$
-1
\$\begingroup\$

You should try below query, also let me know what indexes are on your tables.

  WITH cte1 as (select SUBSTR(nome_evento, instr(nome_evento, '-') + 1) as nome_evento  from contatore)
SELECT count(b.codice),
        b.codice 
FROM cte1 a 
INNER JOIN 
sorgente b 
ON (a.nome_evento = b.codice) 
group by b.codice;
\$\endgroup\$
3
  • 5
    \$\begingroup\$ This is a comment, asking for clarification. Not an answer. \$\endgroup\$ – Mast Aug 23 '20 at 18:52
  • \$\begingroup\$ Hi, thanks but there is an error in the query, SQL syntax. my indexes in table are "id" field in both tables \$\endgroup\$ – pette Aug 25 '20 at 8:08
  • \$\begingroup\$ Hi @Mast can you help me? \$\endgroup\$ – pette Sep 21 '20 at 21:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.