CODEWITHSUNDEEP
sqljoininner-join
user3897585
user3897585

Reputation: 1477

Inner join from 3 tables

Having difficulty with inner joins when trying to display from 3 tables. They are structured as in the picture below:

http://pbrd.co/1odLBZy

What I'm trying to achieve is to select the following from the task table:

How would the SQL query be structured?

Upvotes: 1

Views: 97

Answers (3)

patovega
patovega

Reputation: 343

SELECT t.Name
    , t.OrderInProject
    , t.TimeSpent
    , t.Description
    , t.DueDate
    , u.Username
    , p.Name
FROM task t
INNER JOIN project p
    on t.ProjectId = p.ProjectId
INNER JOIN user u
    on t.WorkerId = u.UserId

Upvotes: 0

drew_w
drew_w

Reputation: 10430

In order to answer something like this it is best to start with a single table then add the columns and other tables testing each addition along the way. That said the following should be close:

SELECT t.Name,
       t.OrderInProject,
       t.TimeSpent,
       t.Description,
       t.DueDate,
       p.Name,
       u.Username
  FROM task t
       INNER JOIN project p ON p.ProjectID = t.ProjectID
       INNER JOIN user u ON u.UserId = t.WorkerID

Best of luck!

Upvotes: 0

Linger
Linger

Reputation: 15048

SELECT p.Name AS ProjectID, u.Username AS WorkerID, t.Name, t.OrderInProject,
       t.TimeSpent, t.Description, t.DueDate
FROM task t
INNER JOIN user u ON t.WorkerID = u.UserID
INNER JOIN project p ON t.ProjectID = p.ProjectID

If you also want to get the Username of the ProjectManagerID then use the following:

SELECT p.Name AS ProjectID, u.Username AS WorkerID, t.Name, t.OrderInProject,
       t.TimeSpent, t.Description, t.DueDate, u2.Username AS ProjectManager
FROM task t
INNER JOIN user u ON t.WorkerID = u.UserID
INNER JOIN project p ON t.ProjectID = p.ProjectID
INNER JOIN user u2 ON p.ProjectManagerID = u2.UserID

Upvotes: 3

Related Questions