Queries with My SQL

Question

Queries with My SQL

Navigation

#1 by (1 votes)

0

I have a SQL query that I can not solve:

Structure of the tables used:

User table

id pk uq ai nn
nombre varchar(25) null
apellido varchar(25) null

Position table

id pk uq ai nn
cargo varchar(50) null
departamento varchar(50) null

User_positioning table

id pk uq ai nn
us_id fk -> usuario.id nn
pos_id fk -> posicion.id nn

The idea is to take from a query all the data you have in user_position through their respective id. The inner join gets very complicated:

SELECT usuario.nombre, usuario.apellido, posicion.cargo, posicion.departamento 
FROM usuario, posicion 
INNER join usuario_posicion 
ON ((usuario.id = usuario_posicion.us_id) AND (posicion.id = usuario_posicion.pos_id ))
WHERE {aca evalúo una dupla nombre_de_usuario/contraseña que también está en la tabla usuario}

MySQL has returned a series of errors due to the mess I'm doing:

1) "The user.id column in on clause is unknown" (with the above mentioned query)

2) "Table / Alias is not unique" (with a similar query in which the order of the tables to operate with join had changed)

Note : I know it's an inconvenience when it comes to thinking about it (I'm making a terrible mess to be honest). I appreciate explanations, comments and more as long as they contribute :). Thank you very much.

mysql sql query select

asked by Faju 23.12.2017 в 05:18

source

1 answer

Help with a function that validates Letters - JavaScript Problem with Youtube Extractor with C #

score 1 · Accepted Answer

Try the following:

 SELECT usuario.nombre, usuario.apellido, posicion.cargo, 
 posicion.departamento 
 FROM usuario inner join usuario_posicion on usuario.id = 
 usuario_posicion.us_id
 inner join posicion on posicion.id = usuario_posicion.pos_id 
 WHERE usuario.nombre like 'aquivaelnombreusuario';

In the table 'user_position' it is not necessary that you have a primary key since it is a relationship table so I recommend you delete the column 'id', comment if it has served you