Problem with Mysql Inner Join

Asked

Viewed 650 times

2

I’m trying to create a query to bring results that are in different tables in the Mysql,3 but I’m not getting it.

I have experience in JOIN in the SQL, but I’m picking up a little bit to do in the Mysql, can you help me? I have already consulted here on the site, but I could not solve my problem. I thought about JOIN and subselects to solve my problem.

I would like to relate and bring as a result the following information:

select nome from usuario 
select nome from entregadores
select nome from status

I thought about that JOIN here, but you’re wrong.

select d.nome, c.nome, a.km, a.valor, a.valor_extra, a.data, b.nome from 
viagens A
inner join b.entregadores 
on (a.usuarios_id=d.usuarios_id)
inner join c.status
on (a.entregadores_id=b.entregadores_id)
inner join d.viagens
on (a.status_id=c.status_id)

Follows the structure of my tables:

Travel chart:

viagens_id
usuarios_id
entregadores_id
forma_pagamento_id
status_id
tipo_valor_id
endereco_origem
numero_origem
contato_origem
complemento_origem
ida_volta
data_horario
valor
valor_extra
observacoes
data_criado
data_modificado
user_modificou
distancia
ativo
deleted

Table users:

usuarios_id
grupo_id
empresas_id
nome
login
senha
email
telefone
codigo_seguranca
departamento
thumb
ramal
data_criado
data_modificado
ativo
deleted

Status table:

status_id
nome
ativo
deleted
padrao

Delivery table:

entregadores_id
veiculos_id
nome
cnh
telefone1
telefone2
placa
email
vencimento_cnh
foto_cnh
foto_doc
data_criado
data_modificado
ativo
deleted

It follows my last Query execution with the columns with the right name, presented another error in Phpmyadmin "I had put some columns with wrong name, tidied and I started this way the query select status.name statusName, trips.value trips, trips.distance, trips.valor_extra, couriers.name of the couriers from travel natural John Couriers natural Join status natural Join users
Phpmyadmin brought error "#1267 - Illegal combination of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) to operation '='""

select status.name statuesName, trips.value trips, trips.distance, trips.valor_extra, couriers.name of the couriers from travel natural John Couriers natural Join status natural Join users

mysql join

  • You can post the attributes of each table?

  • I edited the post with the structure of my tables, with the name of the columns of each one.

2 answers

1

Join has the aliases changed. ON must agree with the table included in Join. Having only the alias statement is wrong and the joins not sequenced, it would look like this:

select d.nome, c.nome, a.km, a.valor, a.valor_extra, a.data, b.nome 
from 
viagens A
inner join entregadores B on (a.entregadores_id=b.entregadores_id) 
inner join status C on (a.status_id=c.status_id)
inner join viagens D on (a.usuarios_id=d.usuarios_id)

  • I tried to run this way on Phpmyadmin and it didn’t work. I edited the post with the structure of my tables and each column of them.

  • When you can, put the error that is being presented.

  • Had put some columns with the wrong name, I tidied up and turned around the query select status.name statusName, trips.value trips.distance, trips.extra value_value_extra, deliverersNome from trips natural Join deliverers natural Join status natural Join usuarios Phpmyadmin brought error "#1267 - Illegal combination of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) to operation '='"

  • The problem is that the collation used in the tables is different, so mysql cannot make the comparisons. In phpadmin you have to select each of the tables involved and check the structure, in the column glue or collation for all colonists who have value they should be the same, in your case I believe that all should be latin1_general_cl, that for all tables.

1

The way you are applying the junction is wrong. viagens A inner join b.entregadores, doesn’t make sense. You have to put the name of the two tables involved in JOIN. The right thing would be viagens A inner join entregadores ON <condição>. As the foreign keys connecting the tables have the same name, you can use the natural Join.

This query here should solve your problem:

select status.nome as statusNome, viagens.nome as viagemNome, viagens.valor 
as viagemValor, viagens.km, viagens.valor_extra, viagens.data, 
entregadores.nome as entregadorNome
from viagens
natural join entregadores
natural join status
natural join usuarios

  • Had put some columns with the wrong name, I tidied up and turned around the query select status.name statusName, trips.value trips.distance, trips.extra value_value_extra, deliverersNome from trips natural Join deliverers natural Join status natural Join usuarios Phpmyadmin brought error "#1267 - Illegal combination of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) to operation '='"

Browser other questions tagged mysql join

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