1

I am trying to make an appointment to return all the municipalities and all the specialties .... being that even in that municipality does not have any provider for such specialty it should be listed with value 0 or null .... at the moment I am with this Query, I need some help.

select 
   cid.txt_cidade, esp.txt_especialidade, count(*) as QTD
from
   tb_associadoespecialidade as assesp
left join 
   tb_especialidade as esp on esp.id_especialidade = assesp.id_especialidade
left join 
   tb_associado as ass on ass.id_associado = assesp.id_associado
left join 
   tb_cidade as cid on cid.id_cidade = ass.id_cidade
where 
   ass.id_categoria = 1 and txt_cidade like 'Tupã'
group by 
   cid.txt_cidade, esp.txt_especialidade
order by 
    cid.txt_cidade desc;

Diagram

diagrama

Result expectancy

TXT_CIDADE ------ TXT_ESPECIALIDADE --------- count QTD
SP -------------- ESPEC01 ------------------- 10
SP -------------- ESPEC02 ------------------- 5
SP -------------- ESPEC03 ------------------- 15
RJ -------------- ESPEC01 ------------------- NULL
RJ -------------- ESPEC02 ------------------- 5
RJ -------------- ESPEC03 ------------------- NULL
Fellipe J. de Sousa
  • 35
  • 9

1 Answers1

0

Try this:

select 
    cid.txt_cidade, esp.txt_especialidade, count(*) as QTD
from
    tb_associadoespecialidade as assesp
left join 
    tb_especialidade as esp on esp.id_especialidade = assesp.id_especialidade
left join 
    tb_associado as ass on ass.id_associado = assesp.id_associado
        and ass.id_categoria = 1
left join 
    tb_cidade as cid on cid.id_cidade = ass.id_cidade
where
    txt_cidade like 'Tupã'
group by 
    cid.txt_cidade, esp.txt_especialidade
order by 
    cid.txt_cidade desc;

When you apply a where clause on a field from a table that is outer joined, you risk unintentionally throwing away records. By putting your desired filtering in the join condition, you still get null values where that criteria is not met. See my answer here for more details.

Community
  • 1
  • 1
KindaTechy
  • 1,041
  • 9
  • 25
  • The same returned only one record when expected was: 5 specialty Tupã ---------- espec1 ------- 1 Tupã ---------- espec2 ------- null Tupã ---------- espec3 ------- null Tupã ---------- espec4 ------- null Tupã ---------- espec5 ------- null – Fellipe J. de Sousa May 11 '17 at 16:38
  • @FellipeJ.deSousa Then I don't understand your question. Neville's comment above. Update your question with the results you are currently getting to give us a better sense of what's going on. – KindaTechy May 11 '17 at 16:47
  • I'll explain what I need, maybe it will help ... List all specialties by municipality even if such specialty is not provided by an associate https://ibb.co/kPpnGQ – Fellipe J. de Sousa May 11 '17 at 16:49
  • In this case it should come the result of the query plus the rest of the spices that exist in tb_especiality as null or 0 – Fellipe J. de Sousa May 11 '17 at 16:55
  • @FellipeJ.deSousa Update your question with the results you are getting to give us a better sense of what's going on. – KindaTechy May 11 '17 at 17:26