Help with select

Question

I’m using the remote select down below:

select
f.no_equipe,
h.no_pessoa_fisica,
a.no_cidadao,
d.dt_ficha
from
tb_cds_cad_individual a,
tb_cds_atend_individual b,
rl_cds_atend_individual_ciap c,
tb_cds_ficha_atend_individual d,
tb_equipe f,
tb_cds_prof g,
tb_pessoa_fisica h
where f.nu_ine = g.nu_ine
and g.nu_cns = h.nu_cns
and d.co_cds_prof = g.co_seq_cds_prof
and a.nu_cns_cidadao = b.nu_cartao_sus
and b.co_seq_cds_atend_individual = c.co_cds_atend_individual
and b.co_cds_ficha_atend_individual = d.co_seq_cds_ficha_atend_indivdl
and c.co_ciap = 727
and d.dt_ficha >= '2017-08-01'
and d.dt_ficha <= '2017-08-30'

In this code he returns me 4 fields: team name, professional name, citizen name and date of the form.

My problem is on the line and a.nu_cns_cidadao = b.nu_cartao_sus where it only shows the line if the number of your card is equal to the number recorded in another table.

I want to change the code so that even if the condition and a.nu_cns_cidadao = b.nu_cartao_sus is not true, it lists the result and in place in the citizen’s name a.no_cidadao something like "NO NAME".

I hope you understand.

Answer 1

Use the LEFT JOIN together with the clause CASE:

    SELECT f.no_equipe,
       h.no_pessoa_fisica,
       CASE
         WHEN a.no_cidadao IS NULL THEN 'GESTANTE NAO CADASTRADA'
         ELSE a.no_cidadao
       END AS no_cidadao,
       d.dt_ficha
  FROM 
       tb_cds_atend_individual b 
       LEFT JOIN tb_cds_cad_individual a ON a.nu_cns_cidadao = b.nu_cartao_sus
       LEFT JOIN rl_cds_atend_individual_ciap c ON b.co_seq_cds_atend_individual = c.co_cds_atend_individual
       LEFT JOIN tb_cds_ficha_atend_individual d ON b.co_cds_ficha_atend_individual = d.co_seq_cds_ficha_atend_indivdl
       LEFT JOIN tb_cds_prof g ON d.co_cds_prof = g.co_seq_cds_prof
       LEFT JOIN tb_equipe f ON f.nu_ine = g.nu_ine
       LEFT JOIN tb_pessoa_fisica h ON g.nu_cns = h.nu_cns
 WHERE c.co_ciap = 727
 AND d.dt_ficha >= '2017-08-01'
 AND d.dt_ficha <= '2017-08-30'

Answer 2

You can use Case When by selecting it and removing it from the 'Where', as below:

select
    f.no_equipe,
    h.no_pessoa_fisica,
case when
     a.nu_cns_cidadao = b.nu_cartao_sus 
then a.nu_cns_cidadao
else 'SEM NOME' end,
     d.dt_ficha
from
   tb_cds_cad_individual a,
   tb_cds_atend_individual b,
   rl_cds_atend_individual_ciap c,
   tb_cds_ficha_atend_individual d,
   tb_equipe f,
   tb_cds_prof g,
   tb_pessoa_fisica h
where f.nu_ine = g.nu_ine
   and g.nu_cns = h.nu_cns
   and d.co_cds_prof = g.co_seq_cds_prof
   and b.co_seq_cds_atend_individual = c.co_cds_atend_individual
   and b.co_cds_ficha_atend_individual = d.co_seq_cds_ficha_atend_indivdl
   and c.co_ciap = 727
   and d.dt_ficha >= '2017-08-01'
   and d.dt_ficha <= '2017-08-30'

I haven’t tested this code, but I believe it solves!

Answer 3

In this case you can make one LEFT JOIN, which makes the result merge keeping the line if the join is not positive. And when selecting the field use the COALESCE to return a second value if the first is NULL. Example:

select
    f.no_equipe,
    h.no_pessoa_fisica,
    COALESCE(a.no_cidadao, 'SEM NOME') AS no_cidadao,
    d.dt_ficha
from
    tb_cds_cad_individual a,
    rl_cds_atend_individual_ciap c,
    tb_cds_ficha_atend_individual d,
    tb_equipe f,
    tb_cds_prof g,
    tb_pessoa_fisica h
    LEFT JOIN tb_cds_atend_individual b ON a.nu_cns_cidadao = b.nu_cartao_sus
where
    f.nu_ine = g.nu_ine
    and g.nu_cns = h.nu_cns
    and d.co_cds_prof = g.co_seq_cds_prof
    and b.co_seq_cds_atend_individual = c.co_cds_atend_individual
    and b.co_cds_ficha_atend_individual = d.co_seq_cds_ficha_atend_indivdl
    and c.co_ciap = 727
    and d.dt_ficha >= '2017-08-01'
    and d.dt_ficha <= '2017-08-30';