Problem with inner join in MySQL

Navigation
1
I'm trying to create a query to bring results that are in different tables in MySQL , but I'm not getting it.

I have experience in JOIN in SQL , but I'm getting a little to do in MySQL , can you help me? I already consulted here on the site, but I could not solve my problem. I thought 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 of this JOIN here, but it is in error. 

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)

The structure of my tables follows:

Travel table: 

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

Users table: 

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

Here is my last Query execution with the columns with the right name, it presented another error in PhpMyadmin "I had placed some columns with the wrong name, I arranged and I surrounded the query select status.name the statusName, trips.value the travelValue, travel.distance, travel.value_extra, deliverers.Name the delivererName from trips natural join handlers natural join status natural join users
PhpMyadmin has the error "# 1267 - Illegal combination of collations (latin1_swedish_ci, IMPLICIT) and (latin1_general_ci, IMPLICIT) for operation '='" "

select status.name the statusName, travel.value the travelValue, travel.distance, travel.value_extra, deliverers.Name the delivererName from trips natural join handlers natural join status natural join users

    
asked by anonymous 21.03.2018 / 03:39

2 answers

0

The join has aliases exchanged. The ON must agree with the table included in the join. Since only the alias declaration is wrong and the joins are 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)
    
21.03.2018 / 05:55
0

The way you are applying the join is wrong. viagens A inner join b.entregadores , that does not make sense. You have to put the name of the two tables involved in the JOIN. The right one would be viagens A inner join entregadores ON <condição> . Because the foreign keys that link 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
    
21.03.2018 / 16:45
CollapsingToolbarLayout equal to Nubank Angular 1.6: Open a uibmodal inside another module using "solve"?