Comparison in several SQL tables

Question

Comparison in several SQL tables

Navigation

#1 by (2 votes)

3

I need to perform a query in SQL validating the following:

I have a parent table that is called elemento and has a field tipoelemento , and two child tables equipo_computo and equipo red , with the fields tipoequipocomputo and tipoequipored respectively.

I need to make a query in the database that validates the type of selected element. If the user selected the tipoelemento = equipocomputo show the tiposdequipodecomputo = "servidor fisico" . But if you selected the tipoelemento = equipored show the records with tipoequipored = "Switch LAN" .

This is the query I'm running:

SELECT DISTINCT e.ID_ELEMENTO , e.NOMBRE
FROM 'pmt_equipos_computo' AS ec, 'pmt_elementos' AS e , 'pmt_equipos_red' AS er 
WHERE e.ID_TIPOELEMENTO = @@drpTipoDispositivo 
AND (ec.ID_ELEMENTO = e.ID_ELEMENTO OR er.ID_ELEMENTO = e.ID_ELEMENTO) 
AND (ec.ID_TIPOEQUIPOCOMPUTO = '2' OR er.ID_TIPOEQUIPORED = '10' );

But it brings me all the elements according to the type of element selected. It is not validating that they are only servers in the case of equipos_computo or that they are only switches in the case of equipos de red .

mysql sql

asked by Mayer Monsalve 27.12.2017 в 16:28

source

1 answer

I need the images to have different div Create named files that include rare characters

score 2 · Accepted Answer

Queries are always complicated when implicit join notation is used, and even more so when the join conditions are expressed in conjunction with the operator OR .

Personally, I would express the query differently, dividing the logic into 2 clauses EXISTS separated by OR :

select e.ID_ELEMENTO, e.NOMBRE
  from pmt_elementos e
 where e.ID_TIPOELEMENTO = @@drpTipoDispositivo
   and (exists(select null
                 from pmt_equipos_computo ec
                where e.ID_TIPOELEMENTO = 'equipocomputo' -- ajusta este valor
                  and ec.ID_ELEMENTO = e.ID_ELEMENTO
                  and ec.ID_TIPOEQUIPOCOMPUTO = '2')
     or exists(select null
                 from pmt_equipos_red er
                where e.ID_TIPOELEMENTO = 'equipored' -- ajusta este valor
                  and er.ID_ELEMENTO = e.ID_ELEMENTO
                  and er.ID_TIPOEQUIPORED = '10'))

The only thing is that I do not know the precise values that you can return @@drpTipoDispositivo . That is why you will see 2 places in the query annotated with -- ajusta este valor . In those 2 places, replace the condition with the correct value that corresponds to that type of element.

Now, if it is impossible for table pmt_equipos_computo to have records that relate to elements of type equipored , and it is also impossible that table pmt_equipos_red have records that relate to elements of type equipocomputo , which would make a lot of sense, then you do not even need those 2 conditions. Then you can simplify the query in this way:

select e.ID_ELEMENTO, e.NOMBRE
  from pmt_elementos e
 where e.ID_TIPOELEMENTO = @@drpTipoDispositivo
   and (exists(select null
                 from pmt_equipos_computo ec
                where ec.ID_ELEMENTO = e.ID_ELEMENTO
                  and ec.ID_TIPOEQUIPOCOMPUTO = '2')
     or exists(select null
                 from pmt_equipos_red er
                where er.ID_ELEMENTO = e.ID_ELEMENTO
                  and er.ID_TIPOEQUIPORED = '10'))