Join in SQL with two tables - users and cells

Asked

Viewed 81 times

-2

I have two tables in my database, one containing all users with id and name and other details, and another table containing all work cells, where tie through the user id who is the leader and who is the supervisor in the cell.

Each cell has 1 leader and 1 supervisor, both fields (columns in the table of cells) have as value the number of the numerical index that references the table of users (foreign key that identifies the leading user, and the user that is the supervisor of the cell).

To know therefore who is the leader of the cell, use the value stored in the leader_id column as the search key in the users table. And the same for supervisor (using the supervisor_id field as the search key in the users table).

The tables are as shown below inserir a descrição da imagem aqui

inserir a descrição da imagem aqui

I would like to create an SQL query that returns the result as follows, using the values of these two tables as parameter: inserir a descrição da imagem aqui

Can anyone help me with this SQL search?

sql join
  • 1

    we can help, you can show how far you’ve come with the query?

  • I was able to do this query: SELECT * FROM Cells INNER JOIN users ON Cells.leader_id = users.id; however, I’m only able to return one of the columns (either using leader_id or supervisor_idas reference only). However in the result I need the column with the name of the leader and supervisor, because I will pass the query to a view in Laravel...

  • 2

    Use two JOIN with the same users table, one to get the leader and one to get the supervisor.

1 answer

2

As @anonimo commented, just add a new join for the same table, but using the "supervisor_id":

SELECT cells.Cell_ID,
       UserLeader.name AS Leader,
       UserSuper.name AS Supervisor
  FROM cells 
 INNER JOIN users UserLeader ON cells.leader_id = UserLeader.id
 INNER JOIN users UserSuper  ON cells.supervisor_id = UserSuper.id

Browser other questions tagged sql join

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