Reputation: 21
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
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
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
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