sql query with conditions

Question

sql query with conditions

Navigation

#1 by (2 votes)

1

trying to solve a request from a client I ran into a problem and wanted to know if it is possible to make an sql query with conditionals.

My problem is the following at the moment I have a query that allows me to bring information from 3 tables.

NGRESO: It is the table that has all the registered teams (brand, serial, date of entry, location)

TEAM: It is the table that has the team's resume, it stores the entry index and the movement index (if it has had location movements) and the current state of the team.

MOVEMENT: It is the table that keeps the changes that have been made to the team since it entered the system until the current moment.

My problem is that I have this query:

SELECT equipo.mov_ide, ingreso.ciu_ide, ingreso.zona_ide, ingreso.ofi_ide, ingreso.micro_ide, ingreso.pun_ide, movimiento.ciu_ideD , movimiento.zona_ideD, movimiento.ofi_ideD, movimiento.micro_ideD, movimiento.pun_ideD, equipo.ide_equipo, ingreso.hard_ide, ingreso.af_fec, ingreso.mar_ide, ingreso.mod_ide,ingreso.ide_prov, ingreso.af_ser, ingreso.af_mac, ingreso.af_simcard, equipo.est_equi
FROM equipo 
LEFT JOIN ingreso ON (equipo.ide_ing = ingreso.ing_ide AND equipo.ide_equipo = ingreso.ide_equipo) 
LEFT JOIN movimiento ON (equipo.mov_ide = movimiento.mov_ide AND equipo.ide_equipo = movimiento.ide_equipo)

and it brings me these results:

Now my problem is that as you see in the image if the first field equipo.mov_ide is greater than 0 means that the team had some movement in the location and this will be stored in movimiento.ciu_ideD , movimiento.zona_ideD, movimiento.ofi_ideD, movimiento.micro_ideD, movimiento.pun_ideD if on the contrary the team has no movements and the first field is zero .. the positions shown are NULL .

A query could be made that only brings the location of the real equipment. in this case if the first field equipo.mov_ide is 0 the real location are the first fields ingreso.ciu_ide, ingreso.zona_ide, ingreso.ofi_ide, ingreso.micro_ide, ingreso.pun_ide

if the first field equipo.mov_ide is greater than 0 the real location is relative to movement movimiento.ciu_ideD , movimiento.zona_ideD, movimiento.ofi_ideD, movimiento.micro_ideD, movimiento.pun_ideD

ILLUSTRATION

Thanks!

sql sql-server mysqli

asked by Andres Felipe Diaz 19.10.2017 в 18:11

source

1 answer

Pass an instance of a class with data to another class in Java count the number of elements that a request laravel 5.5 contains

score 2 · Accepted Answer

First of all, I urge you to get used to using alias in the tables; This improves the reading of the query and makes it easier to understand.

On your question, it sounds like you just need a CASE expression:

SELECT  e.mov_ide, 
        CASE WHEN e.mov_ide = 0 THEN i.ciu_ide ELSE m.ciu_ideD END ciu_ide, 
        CASE WHEN e.mov_ide = 0 THEN i.zona_ide ELSE m.zona_ideD END zona_ide, 
        CASE WHEN e.mov_ide = 0 THEN i.ofi_ide ELSE m.ofi_ideD END ofi_ide, 
        CASE WHEN e.mov_ide = 0 THEN i.micro_ide ELSE m.micro_ideD END micro_ide, 
        CASE WHEN e.mov_ide = 0 THEN i.pun_ide ELSE m.pun_ideD END pun_ide, 
        e.ide_equipo, 
        i.hard_ide, 
        i.af_fec, 
        i.mar_ide, 
        i.mod_ide,
        i.ide_prov, 
        i.af_ser, 
        i.af_mac, 
        i.af_simcard, 
        e.est_equi
FROM equipo e
LEFT JOIN ingreso i
    ON e.ide_ing = i.ing_ide 
    AND e.ide_equipo = i.ide_equipo
LEFT JOIN movimiento m
    ON e.mov_ide = m.mov_ide 
    AND e.ide_equipo = m.ide_equipo
;