How to define the same INNER JOIN for different fields related to the same table

Asked

Viewed 44 times

0

I have a problem which is this, in the i have a table portaria who has a Fk_key with the table usuario, which is who authorizes, and, a relation N to N with the table usuario for those who enter by the ordinance (because, can be two people at the same time) Table: usuarionportaria_solicitante that way I would have an SQL with something like this:

SELECT ups.usuario_usuario_id, portaria.fk_quem_registra FROM portaria
INNER JOIN usuarionportaria_solicitante as ups
ON portaria.portaria_id = ups.portaria_portaria_id
INNER JOIN usuario as usuario
ON usuario.usuario_id = ups.usuario_usuario_id

On my return comes 2 requesting users just as expected from the two records there, however I want to return the names instead of the ids, my obstacle is the portaria.fk_quem_registra, I want you to return something like:

usuarios_solicitantes   |   quem_registrou
José Carlos             |   Froslass
Willian da Silva        |   Froslass

If possible, only with the INNER, LEFT, RIGHT, .... JOIN would be better for learning, I know that a sub-query would solve, I hope you understand, wanted to make this relationship between each user and their id through the INNER JOIN or any other JOIN.

mysql sql
  • 1

    Assuming in your table usuariothere is a field nome_usuariojust replace ups.usuario_usuario_id for usuario.nome_usuario.

  • In fact she has only the id

  • And then from where could you get the name? You can only recover from your database the data that exists there.

  • Through the user table, there contains the id/ user name relationship, only, I’m trying to make relationship with INNER JOIN for this, and at the same time I have 2 fields to return with different users in the case: Who Requests and Who Registers, but both are id’s of the Users table

  • can you answer my question? https://answall.com/questions/469237/identificar-registrations-sequentialof each user

1 answer

5


From your explanation it seems to me that what you want is:

SELECT solicitante.nome_usuario, autorizador.nome_usuario
FROM portaria 
INNER JOIN usuarionportaria_solicitante as ups ON portaria.portaria_id = ups.portaria_portaria_id
INNER JOIN usuario as solicitante ON solicitante.usuario_id = ups.usuario_usuario_id
INNER JOIN usuario as autorizador ON autorizador.usuario_id = ups.usuario_usuario_id;

this is the table usuario participates in two roles: solicitante and autorizador.

  • Just what I needed!

  • help me https://answall.com/questions/469237/identificar-registrants-sequentialof each user

Browser other questions tagged mysql sql

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