CODEWITHSUNDEEP
sqlsql-server
user3897585
user3897585

Reputation: 1477

SQL query with 3 tables

Need some assistance with an SQL query. The relational view is below:

relational tables

The output should show Project Name, Project Manager Username, and Project Due Date. The difficulty is that the above output must be shown for which a specific worker who is assigned to a task inside of that project. So for example, Task 'B.1' is added to Project 'B', a worker is assigned to Task 'B.1' would see the above output for Project 'B'.

Hope this makes sense.

EDIT: I need the ProjectID and WorkerID as well to pass the ID and for a Where clause.

Upvotes: 1

Views: 74

Answers (2)

Sam
Sam

Reputation: 789

try this

select project.projectid, project.name, managers.username, project.duedate, task.workerid from project 
inner join task on project.projectid = task.projectid
inner join user as managers on project.projectmanagerid = managers.userid
inner join user as workers on task.workerid = workers.userid

Upvotes: 2

PMIW
PMIW

Reputation: 757

If I understand what you want, this is another way to do it, it should be something like this :

SELECT Project.Name, User.FirstName, Project.DueDate FROM Project, User, Task WHEN User.ProjectManagerID = User.UserID AND Project.ProjectID = Task.Project ID

I think that's all.

  • The first line you choose what you want.
  • The second, you select all the (necessary) tables
  • The third, you need to make the relation between the ID of the ProjectManager and the ID of the User, to select the firstname of the Manager.
  • Finally, the last line is present to make the link between the task of the project and the project.

Upvotes: 0

Related Questions