query in mysql worbench

Question

query in mysql worbench

Navigation

#1 by (0 votes)

-1

I have a problem to perform a query, which I already have half done but does not do certain things, for example: I have two tables that are related. table1_Evidencias and tabla2_catalogoEvidencias

then the table1_evidence has

id|fk_catalogo_evidencia|ubicación|visita
1 |403                  |HTTP://1 |455
2 |398                  |HTTP://2 |14
3 |368                  |HTTP://3 |566
4 |380                  |HTTP://4 |555

tabla2_catalogoEvidencias

id  |Descripcion |etiqueta_Padre
403 |vitrina     |394 
394 |telcel      |null             
398 |arte        |394            
380 |capacitacion|null
368 |mesa        |null

the query should be like this:

id_evidencia|descripcion evidencia padre|descripcion evidencia hija
1           |vitrina                    |telcel
2           |arte                       |telcel
3           |mesa                       |             
4           |capacitación               |

IS just for example I have managed to make the query when there is a description father and a daughter but I have not managed to show the case when it does not have daughter description

I hope you can help me and I would appreciate it very much Greetings

select 

evi.id as id_evidencia,
CONCAT('https://api.sftracker.com', evi.path) AS Ubicacion,
tmp1.etiqueta_padre as padre_id,
tmp1.Nombre_padre as Etiqueta_padre,
evi.catalgo_evidence_type_id as etiqueta_Secundaria,
cetA.name as etiqueta_Secundaria
from
        catalgo_evidence_types cetA,
        (select distinct
            cetA.id as etiqueta_padre,
            cetA.name as Nombre_padre
        from
            catalgo_evidence_types cetA,
            catalgo_evidence_types cet
        where 
            cetA.id = cet.catalgo_evidence_type_id and

             cetA.is_active=1) tmp1,
             evidences evi
where 
    cetA.catalgo_evidence_type_id=tmp1.etiqueta_padre and
    evi.catalgo_evidence_type_id=cetA.id 

      group by 
        evi.id
    order by 
    evi.id,tmp1.Nombre_padre,cetA.name,cetA.is_active desc

asked by Danny Daniel Quintana 24.10.2018 в 01:18

source

1 answer

dictionaries in dictionary, Python 3 report in mysql with worbench [duplicated]

score 0 · Answer 1

Based on the example structure you put in you could do something like this:

    select ev.id, cev.descripcion as 'descripcion_evidencia_padre', 
   (select descripcion from tabla2_catalogoEvidencias cev2 where cev2.id = cev.etiqueta_padre) 
    as 'descripcion_evidencia_hija' 
    from tabla1_evidencias ev 
    inner join tabla2_catalogoEvidencias cev on ev.fk_catalogo_evidencia = cev.id
    order by id asc

I hope and it will be useful. Greetings.