Two results same column

Question

Two results same column

Navigation

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

2

I have a company table with the id and name columns. And another table operations with the columns send_id , recib_id . I want to get the results name of send_id and receive_id . And how do I get the two different results in php?

SELECT
companias.id, companias.nombre, operaciones.envia_id, 
operaciones.recibe.id
INNER JOIN companias
ON operaciones.recibe_id=companias.id

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_BOTH) ) {
 echo "
   <tr>
   <td>$row[nombre]</td>
   <td>$row[nombre]</td>
   </tr>
 ";
}

So I get the name of the person who receives, now I need the name of the sender to appear.

sql php

asked by OJR 16.03.2018 в 15:21

source

2 answers

Do .htaccess files work on Google Cloud VMs? Join in laravel

score 2 · Answer 1

Assuming that your table where id y nombre is called usuarios and that the other table is called envios , the following query should work.

select envia.nombre, recibe.nombre 
from usuarios as u 
join envios as envia on u.id = envia.envia_id 
join envios as recibe on u.id = recibe.recibe_id;

score 1 · Answer 2

It is required to do a double JOIN between the tables operaciones and companias , one for the one that sends and another for the one that receives.

Since you will have to use twice the field nombre of the table companias , once for the sender and again for the receiver, you must use an alias to differentiate them and then read them in a different way.

The query would then be like this:

SELECT 
    e.id, 
    e.nombre emisor, 
    r.nombre receptor, 
    o.envia_id, 
    o.recibe.id 
FROM operaciones o 
INNER JOIN companias e ON o.envia_id=e.id
INNER JOIN companias r ON o.recibe_id=r.id

Then, to print the data, you will use the alias names of the columns, in this case, emisor and receptor :

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_BOTH) ) {
 echo "
   <tr>
   <td>$row["emisor"]</td>
   <td>$row["receptor"]</td>
   </tr>
 ";
}

NOTE: If you are not going to use the columns envia_id and recibe_id to display them on the screen, you do not need to put them in SELECT .