Extracting same field to 2 different places in one query

[bcahillane]

Here is a simplified view of the tables and fields relevant to my query:


Table: Workorder
Fields: workorderid (key), requesterid

Table: Workorderstate
Fields: workorderid (key), technicianid

Table: User
Fields: userid(key), name

Workorder and Workorderstate are related by workorderid. Requesterid is related to userid. Technicianid is also related to userid. I am bound to using this table and relation structure.

I would like a query that pulls all workorderid's and lists the related technician's name and requester's name. My problem is that I do not know how to pull the name field from user twice in one query where one instance refers to the technician and another refers to the requester.

 

[TonyGroves]

This has also been posted to the Ansi SQL forum.

 

[r937]

looks like it might have been removed from the ANS SQL forum

select WO.workorderid
     , R.name as requestor
     , T.name as technician
  from Workorder as WO
inner
  join Workorderstate as WOS
    on WOS.workorderid = WO.workorderid
inner
  join User as R
    on R.userid = WO.requesterid
inner
  join User as T
    on T.userid = WOS.technicianid

r937.com | rudy.ca