Lookup multiple ids in separate table with 2 subqueries

Question

I have a query from a timesheets tables that I am using to select a submitter, approver, and a date.

SELECT PRTIMESHEET.POSTED_TIME,
  PRTIMESHEET.PRRESOURCEID as "SUBMITTER",
  PRTIMESHEET.PRAPPROVEDBY as "APPROVER"
FROM PRTIMESHEET
WHERE rownum < 10;

In 1 table I have a list of all employees. The PRTIMESHEET.PRRESOURCEID and PRTIMESHEET.PRAPPROVEDBY both link to SRM_RESOURCES.ID.

That table has data like the following.

How can I run 1 query to combine the 2 tables and get the names instead of IDs? Something like this:

I have tried a subselect like this to do joins, but with no luck.

select prtimesheet.posted_time as "TIMESHEET DATE", 
  (
  select prtimesheet.prresourceid as "SUBMITTER"
  from prtimesheet
  where prtimesheet.prresourceid = srm_resources.id
  ),
  (
  select prtimesheet.prapprovedby as "APPROVER"
  from prtimesheet
  where prtimesheet.prapprovedby = srm_resources.id
  )
FROM prtimesheet, srm_resources;

I am getting the following error: ORA-01427: single-row subquery returns more than one row

Answer 1

You're permitted to do multiple joins in a single statement. You can do this even when joining on the same table by using aliases.

I created a very quick example which is similar to your case. I just used ids instead of a time, and I stored the whole name as a single field; you'll just have to concatenate the first and last name on your own ;)

Schema and Data

CREATE TABLE prtimesheet (
  id numeric,
  submitter_id numeric,
  approver_id numeric
);

 CREATE TABLE srm_resources (
   person_id numeric,
   name text
 );

 INSERT INTO prtimesheet VALUES(1, 1000, 1001), (2,1000,1002), (3,1002, 1001), (4,1001,1002);

 INSERT INTO srm_resources VALUES (1000,'Jack Ryan'), (1001, 'Bart Mancuso'), (1002, 'James Greer');

Multiple joins on same table using aliases

SELECT prtimesheet.id, sub_table.name AS submitter_name, app_table.name AS approver_name
FROM prtimesheet
JOIN srm_resources AS sub_table
ON prtimesheet.submitter_id = sub_table.person_id
JOIN srm_resources AS app_table
ON prtimesheet.approver_id = app_table.person_id
ORDER BY id;

This should fix you right up!

Here is a SQL Fiddle for you to play around with if you like.

Edit: And as suggested...

The Oracle version: Schema and Data

CREATE TABLE prtimesheet (
  id number,
  submitter_id number,
  approver_id numeric
 );

  CREATE TABLE srm_resources (
     person_id number,
     name varchar(25)
  );

  INSERT ALL
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(1, 1000, 1001) 
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(2,1000,1002)
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(3,1002, 1001)
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(4,1001,1002)
  SELECT * FROM DUAL;

 INSERT  ALL
 INTO srm_resources(person_id, name) VALUES(1000,'Jack Ryan') 
 INTO srm_resources(person_id, name) VALUES(1001, 'Bart Mancuso')
 INTO srm_resources(person_id, name) VALUES(1002, 'James Greer')
 SELECT * FROM DUAL;

The Oracle version: Joins and aliases

SELECT prtimesheet.id, sub_table.name AS submitter_name, app_table.name AS approver_name
FROM prtimesheet
JOIN srm_resources sub_table
ON prtimesheet.submitter_id = sub_table.person_id
JOIN srm_resources app_table
ON prtimesheet.approver_id = app_table.person_id
ORDER BY id;

Here is the Oracle SQL Fiddle!