INNER JOIN with equal fields in a table - Error: # 1066 - Not unique table / alias: 'tb_user' [duplicate]

Question

INNER JOIN with equal fields in a table - Error: # 1066 - Not unique table / alias: 'tb_user' [duplicate]

Navigation

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

1

I have 3 tables as follows:

Intb_user,Ihavetwotypesofusers(buyer,seller)inanENUMfieldtodifferentiatetheusertype.

IntheshoppingcartIhavetheuserIDs,product,andtransactionnumber.Ilink2timestotb_usuariotoknowwhoboughtandwhosold.

IwanttoperformaSELECTthatbringsthenames(buyer,seller,product),butwhenIdothequeryhappensthefollowingerrors:

1-SELECTtb_user.namethe'buyer',tb_user.namethe'seller',tb_products.namethe'product'FROMpurchases INNERJOINtb_usuarioONcompras.id_comprador=tb_usuario.id_usuario INNERJOINtb_usuarioONcompras.id_vendedor=tb_usuario.id_usuario INNERJOINtb_animaisONcompras.id_produto=productos.id_produtoWherebuy_id=1;

Error:#1066-Notuniquetable/alias:'tb_user'

**DoIneedtocreateatableforeachusertypebecauseofthe2aliases?**

Or

2-SELECTtb_user.namethe'buyer',tb_user.namethe'seller',tb_products.namethe'product'FROMpurchases INNERJOINtb_usuarioONcompras.id_comprador=tb_usuario.id_usuario INNERJOINtb_animaisONcompras.id_produto=productos.id_produtoWherebuy_id=1;

Hereitreturnstwicethesamename(IknowitiswrongbecauseIdonotspecifywhoistheseller).

I do not know if it is necessary to create 3 tables or if there is a way to perform 2 times joining of tb_user with the products. Could you help me?

mysql join query select

asked by anonymous 27.11.2016 / 19:33

2 answers

.ssh folder is not created when generating the command ssh-keygen -t rsa -C "[email protected]" Listing Folder Files with PHP

score 0 · Answer 1

The problem is that since you have two tables tb_usuario being related, you need to tell which one you refer to by 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 have determined that the first tb_usuario will be named comprador and the second as vendedor just putting a nickname shortly after the table name. We also checked that there was a tb_produtos table being referenced in the field relation, and that it does not exist in the FROM and / or INNER JOIN clause.

score -1 · Answer 2

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

Because it does not create two tables (buyers, sellers) so they get separated. In your search I believe that all 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 will not have the error any more.

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