how to filter fields with several and and with or in mysql?

Question

how to filter fields with several and and with or in mysql?

Navigation

#1 by (1 votes)

0

My query is as follows

select * from paciente pc 
join persona per on per.dni=pc.dni
join det_pac_tra dp on dp.dni=pc.dni 
where  pc.cod_centro_salud='3'  and pc.cod_situacion='1' and dp.frecuencia_fase1 = '1' or  dp.frecuencia_fase2='2'

I explain to you I have to bring all the patients who have the cod_centro=3 and that their dp.frecuencia_fase1 = '1' or dp.frecuencia_fase2='2' but when executing my query I get all the patients of all the cod_centro that is wrong

mysql

asked by ingswsm 28.08.2017 в 19:53

source

1 answer

Error "was not declared in the scope" Problem to get data from a table and store it in a different second table

score 1 · Answer 1

Your problem is how you are using the conditions AND and OR . The precedence indicates that the AND and the OR are evaluated at the end, I will rewrite it with parentheses so that it is clear to you why you are getting those results:

select * 
from paciente pc 
inner join persona per 
    on per.dni=pc.dni
inner join det_pac_tra dp 
    on dp.dni=pc.dni 
where ( pc.cod_centro_salud='3' 
        and pc.cod_situacion='1' 
        and dp.frecuencia_fase1 = '1')
or dp.frecuencia_fase2='2';

In this case, if dp.frecuencia_fase2='2' is true, then all WHERE is true.

You can rewrite it in the following way:

select * 
from paciente pc 
inner join persona per 
    on per.dni=pc.dni
inner join det_pac_tra dp 
    on dp.dni=pc.dni 
where pc.cod_centro_salud='3' 
and pc.cod_situacion='1' 
and (dp.frecuencia_fase1 = '1'
     or dp.frecuencia_fase2='2');

Or what is simplest would be to use IN :

select * 
from paciente pc 
inner join persona per 
    on per.dni=pc.dni
inner join det_pac_tra dp 
    on dp.dni=pc.dni 
where pc.cod_centro_salud='3' 
and pc.cod_situacion='1' 
and dp.frecuencia_fase1 in ('1','2');