How to join 2 fields of a table to only 1 field of another table?

Question

How to join 2 fields of a table to only 1 field of another table?

Navigation

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

0

I have 2 tables in my DB, one is "users" with the fields "id", "name" and "surnames"

The second "sales" has the fields "id_venta" "id_vendedor" and "id_comprador" and what I want is to generate an html table in which the names and surnames of the buyer and seller appear.

Id_venta-------- Vendedor --------- Comprador 

1----------------Manuel Perez-------- Pepe Fernandez

It occurs to me how to do it with a join between the 2 tables to take for example the name of the seller, but how can I extract the name of the seller and the buyer at the same time? I'm using codeigniter as a framework.

$this->db->select('*');
$this->db->from('ventas');
$this->db->join('usuarios','usuarios.id = ventas.id_vendedor');

mysql php codeigniter

asked by Juan Luis 19.06.2017 в 10:30

source

2 answers

0

You did the right thing because apparently id_seller and buyer_id refer to user_id. Now if you want to have the value of the user's first and last name in a single column, concatenate the attributes with a "+":

SELECT *, 
compradores.nombres + compradores.apellidos as nombre_comprador,
vendedores.nombres + vendedores.apellidos as nombre_vendedor

Now you must bear in mind that if a column, name or surname is NULL, when concatenating your value it will be a gap, to compensate that you can optimize your sentence with ISNULL (), in this way

IsNull(compradores.nombre, '') + IsNull (compradores.apellidos, '')

With this, if the 1st value is null, it will return ''. I hope I have contributed to your answer and that it helps you, greetings.

answered by 19.06.2017 в 15:00

random in javascript Doubt Goodby / csv component of the Packagist

score 0 · Accepted Answer

At the end I made a join 2 times to the same table with 2 different aliases and aliased the fields that I have to differentiate:

$this->db->select('*, compradores.nombre as nombre_comprador, vendedores.nombre as nombre_vendedor');
$this->db->from('ventas');
$this->db->join('usuarios as vendedores','usuarios.id = ventas.id_vendedor');
$this->db->join('usuarios as compradores','compradores.id = ventas.id_comprador');