Retrieve values from 3 tables

Question

Retrieve values from 3 tables

Navigation

#1 by (0 votes)

0

I have three tables servicios , usuarios e instaladores2 , as shown here:

usuarios                 servicios                      instaladores2
---------------          ---------------------          --------------
id_usuario               id_contrato                    id_instalador
nombre_completo          nombre_cliente                 nombre_completo
                         domicilio_instalacion 
                         colonia
                         poblacion
                         telefono_local
                         telefono_cel_1
                         telefono_cel_2
                         telefono_cel_3 
                         nombre_contacto
                         Comentario
                         hora_instalacion 
                         cita_confirmada
                         id_instalador
                         id_gestiono

I need to make a query that brings me the name of who I manage, at the moment I have the code like this:

$sql = "SELECT 
                    'id_contrato',
                    'nombre_cliente',
                    'domicilio_instalacion',
                    'colonia',
                    'poblacion',
                    'telefono_local',
                    'telefono_cel_1',
                    'telefono_cel_2',
                    'telefono_cel_3',
                    'nombre_contacto',
                    'Comentario',
                    ti.nombre_completo as instalador,
                    hora_instalacion,
                    cita_confirmada
                FROM 
                    'bd_servicios' as tc
                        inner join 
                    bd_instaladores2 as ti on (tc.id_instalador = ti.id_instalador)
                WHERE 
                    fecha_instalacion = '$fecha_instalacion'
                    $where
                ORDER BY 
                    instalador, hora_instalacion
                    ;";

What up to now does this code that does work, is a list of the services and I pull the name of the installer from the table instaladores2 , since in the table servicios I only store the id_instalador .

Now what I need is that from the id_gestiono field, which is stored in the servicios table, get the name from the user table, which is in the nombre_completo field.

sql php

asked by Pedro Jimenez 14.06.2017 в 20:26

source

1 answer

how to check the internet connection? Error running my Ionic 3 app

score 0 · Accepted Answer

$sql = "SELECT 
tc.id_contrato,
tc.nombre_cliente,
tc.domicilio_instalacion,
tc.colonia,
tc.poblacion,
tc.telefono_local,
tc.telefono_cel_1,
tc.telefono_cel_2,
tc.telefono_cel_3,
tc.nombre_contacto,
tc.Comentario,
ti.nombre_completo as instalador,
hora_instalacion,
cita_confirmada,
u.nombre_completo
FROM bd_servicios tc
inner join bd_instaladores2  ti on tc.id_instalador = ti.id_instalador
inner join usuarios u on tc.id_gestiono = u.id_usuario
WHERE  fecha_instalacion = '$fecha_instalacion' 
ORDER BY instalador, hora_instalacion";

You have to make another Join to the user table and link the corresponding IDs and in the select bring the name of the user with its respective acronym.

Greetings.