CODEWITHSUNDEEP
sqlsql-serverdatabase
Bao
Bao

Reputation: 21

SQL Query multi reference to a table

I have two tables Task and User

Task       PRIMARY KEY
IdTask  
TaskName
IdHandler  FOREIGN KEY REFERENCES IdUser
IdCreator  FOREIGN KEY REFERENCES IdUser

User
IdUser     PRIMARY KEY
Name

How to query with IdHandler and IdCreator reference to IdUser

Expected Result: TaskName, Name(Handler), Name(Creator)

PS: I'm also do not know if I can put Foreign Keys like that, at least SQL Server let me do it.

Upvotes: 0

Views: 128

Answers (3)

Abra
Abra

Reputation: 20924

Here is an alternative query not using JOINs

select T.NAME_TASK
      ,(select U1.NAME from USERS U1 where U1.ID_USER = T.ID_HANDLER) as HANDLER
      ,(select U2.NAME from USERS U2 where U2.ID_USER = T.ID_CREATOR) as CREATOR
  from TASKS T

Refer to this db<>fiddle

Upvotes: 0

Kyle Leonor
Kyle Leonor

Reputation: 13

Please see below query. 

Select  
    tt.TaskName, 
    userHandler.Name as 'UserHandler',
    userCreator.Name as 'UserCreator'
from 
    TasKTable tt
left join
    User userHandler on userHandler.IdUser = tt.IdHandler
left join
    User userCreator on userCreator.IdCreator = tt.IdHandler

select all field from all table

 Select 
      tt.*,
      userHandler.*,
      userCreator.*
   from 
      TasKTable tt left join
      User userHandler on userHandler.IdUser = tt.IdHandler left join
      User userCreator on userCreator .IdCreator = tt.IdHandler

You may also check this SQL Tutorial for reference.

Upvotes: 0

Circle Hsiao
Circle Hsiao

Reputation: 1987

SELECT TaskName, h.Name [HandlerName], c.Name [CreatorName]
FROM Task t
INNER JOIN User h ON t.IdHandler = h.IdUser
INNER JOIN User c ON t.IdCreator = c.IdUser

Upvotes: 1

Related Questions