INNER JOIN with equal fields in a table - Error: #1066 - Not Unique table/alias: 'tb_usuario'

Question

I have 3 tables as follows:

At tb_usuario, I have two types of users (buyer, seller) in an ENUM field to differentiate the type of user.

In the shopping cart I have the user Ids, product and transaction number. I relate 2 times to tb_usuario to know who bought and who sold.

I want to perform a SELECT that brings the names(buyer, seller, product), but when I perform the query happens the following errors:

1 - SELECT tb_usuario.name 'buyer', tb_user.name 'seller', tb_products.name 'product' FROM purchases INNER JOIN tb_usuario ON compras.id_comprador=tb_usuario.id_usuario INNER JOIN tb_usuario ON compras.id_vendedor=tb_usuario.id_usuario INNER JOIN tb_animals ON shopping.id_product=products.id_product Where id_compra=1;

Error: #1066 - Not Unique table/alias: 'tb_usuario'

** I need to create a table for each user type due to the 2 equal aliases ?? **

Or

2 - SELECT tb_usuario.name 'buyer', tb_user.name 'seller', tb_products.name 'product' FROM purchases INNER JOIN tb_usuario ON compras.id_comprador=tb_usuario.id_usuario INNER JOIN tb_animals ON shopping.id_product=products.id_product Where id_compra=1;

Here returns twice the same name(I know it is wrong because I do not specify which is the seller).

I don’t know if it is necessary to create 3 tables or if there is a way to perform the joining 2 times of tb_usuario with the products. Could you help me

Answer 1

The problem is that as you have two tables tb_usuario being related, you need to tell which one you refer to using an alias for the table

SELECT comprador.nome as 'comprador',
       vendedor.nome as 'vendedor',
       produto.nome as 'produto'
  FROM compras compra
       INNER JOIN tb_usuario comprador ON compra.id_comprador = comprador.id_usuario
       INNER JOIN tb_usuario vendedor ON compra.id_vendedor = vendedor.id_usuario
       INNER JOIN tb_animais produto ON compra.id_produto = produto.id_produto
 WHERE compra.id_compra = 1

Note that I determined that the first tb_usuario will be termed as comprador and the second as vendedor just by putting a nickname right after the table name. We also found that there was a table tb_produtos referenced in the field relation, which does not exist in the clause FROM and/or INNER JOIN.

Answer 2

Hi, most likely your error is in using (INNER JOIN tb_usuario ) 2 times.

Because it does not create two tables (buyers, sellers) so they are separated. In your search I believe that every sale or purchase has to be related to 1 seller and 1 buyer.

Hence you use (INNER JOIN buyers c ON ...) and (INNER JOIN sellers v ON ...) So you won’t have the mistake anymore.

Depending on the situation you can opt for LEFT JOIN or other JOIN, but there is another issue that I believe will not be problem.