report in mysql with worbench [duplicated]

Question

report in mysql with worbench [duplicated]

Navigation

#1 by (0 votes)

-1

I have this quetry that is not letting me perform the consultations in a good way

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

the tables are

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

and what I want to get is

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

Greetings

asked by Danny Daniel Quintana 24.10.2018 в 19:26

source

1 answer

query in mysql worbench Error in switch case statements

score 0 · Answer 1

I found the answer and I hope I can serve someone here

select

v.id as Visita_id,

v.timeSchedule as Fecha_Programada,

v.timeStart as Fecha_Inicio,

v.timeEnd as Fecha_Fin,

evi.id as id_evidencia,

if(tmp.padre_id='',evi.catalgo_evidence_type_id,tmp.padre_id) as id_Etiqueta_Principal,

if( tmp.padre_nombre='',tmp.etiqueta_nombre,tmp.padre_nombre) as Nombre_Etiqueta_Principal,

tmp.etiqueta_id as id_Etiqueta_Secundaria,

tmp.etiqueta_nombre as Nombre_Etiqueta_Secundaria,

concat("Foto",' ', @f := @f +1) as id_Foto,

CONCAT('https://api.sftracker.com', evi.path) AS Ubicacion,

u.email,

pc.id as id_PDV,

pc.name as Nombre_PDV,

pc.bussinesName as Cadena

from

(Select Distinct

    coalesce(parent_labels.parent_id,'') as padre_id,


    coalesce(parent_labels.parent_name,'') as padre_nombre,


    catalgo_evidence_types.id as etiqueta_id,


    catalgo_evidence_types.name as etiqueta_nombre

From

    catalgo_evidence_types 

    left Join


    (
    Select Distinct

        catalgo_evidence_types.id As parent_id,


        catalgo_evidence_types.name As parent_name,


        catalgo_evidence_types.is_active As parent_is_active


    From


    catalgo_evidence_types

Left Join

    catalgo_evidence_types cet

On catalgo_evidence_types.id = cet.catalgo_evidence_type_id

    Where


        catalgo_evidence_types.project_id = 16 
And

        catalgo_evidence_types.is_active = 1
    ) as parent_labels

On catalgo_evidence_types.catalgo_evidence_type_id = parent_labels.parent_id

Where

    catalgo_evidence_types.project_id = 16


    Order By


    catalgo_evidence_types.is_active desc, 

    'padre_nombre', 

    'etiqueta_nombre'
) tmp,


visits v,

project_customers pc,

project_users pu,

users u,

evidences evi,

(SELECT @f := 0) fAux

where

evi.visit_id=v.id and

evi.catalgo_evidence_type_id=tmp.etiqueta_id and

v.project_customer_id = pc.id and

v.project_user_id = pu.id and

pu.user_id = u.id and

pu.project_id = 16 and

v.timeschedule between '2018-10-20 00:00:00' and '2018-10-23 23:59:59'