Join Mysql Tables with similar field names

Asked

Viewed 26 times

-3

I have the following tables:

  1. client : id_client, name, surname, birth date
  2. house : id_casa, house, fk_cliente, fk_bairro
  3. car : id_car, model, fk_customer
  4. neighborhood: id_bairro, name

I want to perform a query on these tables where I consult all customer data, color of their homes, name of neighborhoods and their homes.

What I tried to:

select `id_cliente`, `nome` as `nome_cliente`, `sobrenome`, `data_nascimento`, `cor` as `cor_da_casa`, `nome` as `nome_bairro` from `cliente`, `casa`, `bairro`;

The mistake I’m getting:

ERROR 1052 (23000): Column 'nome' in field list is ambiguous

mysql

  • Required reading: https://answall.com/questions/6441/qual-%C3%A9-a-difference%C3%A7a-entre-Inner-Join-e-outer-Join

  • Other reading: https://stackoverflow.com/questions/12364602/mysql-inner-join-with-where-clause

  • Other: https://answall.com/questions/149407/diferen%C3%a7a-entre-right-Join-e-left-Join? noredirect=1&lq=1

2 answers

2

When two columns have the same name in a Join (it is implicit, simply listing names, as your case or explicit using the word JOIN), you need to tell which table you are referring to when using the field in a SELECT.

Can do with full table name:

SELECT cliente.nome AS nomedocliente FROM `cliente`, `casa`, `bairro`
       ------------ aqui você disse de onde vem o nome, não é mais ambíguo...

Or with alias:

SELECT x.nome AS nomedocliente FROM `cliente` x, `casa` y, `bairro` z
      --- x é o ALIAS (apelido)              --- que você define aqui.

In case you can do tabela AS alias or simply tabela alias

-3

For this you would need in thesis, use Inner Join.

INNER JOIN selects records that have corresponding values in both tables.

Through this example, you will get where you want.

See working on Sqlfiddle

SELECT cli.*, cs.cor as casa
FROM cliente AS cli
JOIN casa AS cs ON cli.id = cs.cliente_id

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.