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;

enter image description here

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.

enter image description here

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

enter image description here

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

• KisnardOnline: In the meantime I have solved this in Excel in like 5 minutes(Vlookup), but I am still curious if someone can even just provide an example. Thanks so much.

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!