0

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

1
  • 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. Jun 19, 2015 at 19:59

1 Answer 1

2

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!

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.