CODEWITHSUNDEEP
sqlinner-join
harryovers
harryovers

Reputation: 3138

Join same table to multiple fields

I have this SQL and i am trying to join a table of people's login names to a table of tasks that contains several user ids

  SELECT Task.TaskID
      ,Project.Project
      ,Task.Task
      ,Task.Description
      ,Task.OwnerLoginID //shown as Login.UserName
      ,Task.SubmitterID //shown as Login.UserName
      ,Task.IsVisible
  FROM Task
  INNER JOIN Project ON Task.ProjectID = Project.ProjectID
  /*
  INNER JOIN Login ON Task.SubmitterID = Login.LoginID
  INNER JOIN Login ON Task.OwnerLoginID = Login.LoginID
  */
  WHERE IsVisible = 1

i get an error in the commented out lines at the bottom where am i going wrong?

Upvotes: 3

Views: 2988

Answers (2)

Mark D
Mark D

Reputation: 5648

try assigning alias names to the tables e.g.

SELECT Task.TaskID
      ,Project.Project
      ,Task.Task
      ,Task.Description
      ,submitted.UserName
      ,owner.UserName
      ,Task.IsVisible
  FROM Task
  INNER JOIN Project ON Task.ProjectID = Project.ProjectID
  INNER JOIN Login submitted ON Task.SubmitterID = submitted.LoginID
  INNER JOIN Login owner ON Task.OwnerLoginID = owner.LoginID
  WHERE IsVisible = 1

Upvotes: 9

thevan
thevan

Reputation: 10344

If you want to JOIN the same table means, Give like this 

  INNER JOIN Login L ON Task.SubmitterID = L.LoginID
  INNER JOIN Login LA ON Task.OwnerLoginID = LA.LoginID

Upvotes: 3

Related Questions