Multiple Lookups to a HR_TABLE

[ashcarrot]

SELECT check_id, created_by, locked_by, updated_by FROM reports,HR_DATA WHERE ........

Where created_by , locked by and updated_by each defines a person by a unique id say 101 though i need to lookup the name of the person which are stored in HR_data as first_name and last_name and its unique id is usr_id

ie

HR_DATA(usr_id,first_name, last_name)
REPORTS (check_id, created_by, locked_by, updated_by)

i need to be able to look up the full name of each of these, if it was just one name i could look it up using

SELECT check_id, created_by, locked_by, updated_by FROM reports,HR_DATA WHERE usr_id = created_by

Though as i need it for 3 fields this wont work

Anyopne know how to do this efficiently?

Thanks
Ash

 

[sem]

You may join HR_DATA table multiple times:

SELECT check_id, created_by, locked_by, updated_by,
a.name, b.name, c.name
FROM reports,HR_DATA a,HR_DATA b,HR_DATA c WHERE a.usr_id = created_by and b.usr_id = locked_by and c.usr_id=updated_by

If not all fields are filled, you may use outer join.

Regards, Dima

 

[ashcarrot]

Yeah the locked_by and updated_by may not have any links so in that case i need it to return blank

So would

SELECT check_id, created_by, locked_by, updated_by,
a.name, b.name, c.name
FROM reports,HR_DATA a,HR_DATA b,HR_DATA c OUTER JOIN a.usr_id = created_by and b.usr_id = locked_by and c.usr_id=updated_by

work?

 

[sem]

Try to read whole message.

Regards, Dima