Sentence for an inner join to two fields in a table

Question

Sentence for an inner join to two fields in a table

Navigation

#1 by (3 votes)
#2 by (1 votes)

0

I have Tables A and B. In B I have two fields that contain the Ids of A to make relationships in a php report. However I run into a problem: that I can not do a INNER JOIN to show those two fields;

TABLA-A
id      Nombre
================
 1      Andres
 2      Juan

TABLA-B
id      id_Primer_persona      id_Segunda_persona
===================================================
1       1                      2

Well then, I have those two tables. In A I have the data of the person and B is a table that can have two people per record (these tables are for example, the real ones have a lot of data and I can not change the development). I would normally do this INNER JOIN :

SELECT tablaB.id, tablaA.nombre as Nombre1 
FROM tablaB
INNER JOIN tablaA on tablaA.id = tablaB.id_Primer_persona

Result:

id nombre1
==========
1  Andres

That code works to get the first person, but then, if I want to take a record that shows me the name of the first and second person, how do I do it? Thanks.

mysql join

asked by Andress Blend 24.02.2017 в 16:39

source

2 answers

1

What I would do is make JOINS to the same table you should work like this:

SELECT A.id, B.nombre as Nombre1,  
FROM tablaA AS A
INNER JOIN tablaB AS B on A.id = B.id_Primer_persona
INNER JOIN tablaA AS A2 on A.id = B.id_Segunda_persona

answered by 24.02.2017 в 16:47

Wait for an append within a cycle How to give my style to the footer, with an image ...?

score 3 · Accepted Answer

Make INNER JOIN to TablaA twice, with different aliases:

SELECT
    tablaB.id,
    tablaA1.nombre as nombre1,
    tablaA2.nombre as nombre2
FROM tablaB 
INNER JOIN tablaA as tablaA1 on tablaA1.id = tablaB.id_primer_persona
INNER JOIN tablaA as tablaA2 on tablaA2.id = tablaB.id_segunda_persona;

That would give you the result:

id    nombre1    nombre2
========================
1     Andres     Juan

This would only work if both IDs exist in the row of tablaB ; if there was not, you would have to adjust the query using LEFT JOIN , perhaps.