mysql nested queries

Question

mysql nested queries

Navigation

#1 by (1 votes)

0

I have 2 tables of agreements and one of associates, I consult the agreements because an agreement can have several associates I get all the members' nit and then I need to obtain all the names and surnames of the associates from the associated table try with this query but it did not work

       Select convenio.id_Convenio, convenio.nit_Fuente, convenio.sede, convenio.nit_asociado, convenio.nit_socio2 as nit2, convenio.nit_socio_3 as nit3, asociados.Nom_asociado as nom1, asociados.Apell_asociado as nom2 
From convenio

(SELECT asociados.Nom_asociado as soc2 , asociados.Apell_asociado as ap2,
convenio.nit_socio2
FROM convenio
LEFT JOIN asociados on convenio.nit_socio2 = nit2

)
(SELECT asociados.Nom_asociado as soc3 , asociados.Apell_asociado as ap3,
convenio.nit_socio2
FROM convenio
LEFT JOIN asociados on convenio.nit_socio_3 = nit3
)

LEFT JOIN asociados on convenio.nit_asociado = asociados.nit_asociado 
where convenio.id_Convenio like '4'

please I need help

mysql

asked by Jeferson Martinez 06.03.2017 в 20:39

source

1 answer

Send image from android to web server api Redirect to URL in java REST? or not redirect?

score 1 · Accepted Answer

Ok, I think I understood. You need to join the table asociados 3 times, so you will need to assign aliases to distinguish the 3 joins:

Select c.id_Convenio,
       c.nit_Fuente,
       c.sede,
       c.nit_asociado,
       c.nit_socio2 as nit2,
       c.nit_socio_3 as nit3,
       a1.Nom_asociado as soc1,
       a1.Apell_asociado as ap1,
       a2.Nom_asociado as soc2,
       a2.Apell_asociado as ap2,
       a3.Nom_asociado as soc3,
       a3.Apell_asociado as ap3
From convenio c
left join asociados a1 on a1.nit_asociado = c.nit_asociado
left join asociados a2 on a2.nit_asociado = c.nit_socio2
left join asociados a3 on a3.nit_asociado = c.nit_socio_3
where c.id_Convenio like '4'

Note apart, the last condition like '4' is a little weird. If you are not going to use % , you can simply use the operator = :

where c.id_Convenio = '4'