CODEWITHSUNDEEP
oracle11goracle-apex
PTTHomps
PTTHomps

Reputation: 1499

How do I substitute values from a second table (multiple times) in a single query in Oracle SQL/APEX

Suppose I have an employee table “DB_EMPLOYEE” with the following values for EMPID and NAME

EMPID   NAME
0       Bob
1       Joe
2       Carl
3       Wendy

Next, I have another table listing audits, “ICQA_AUDITS” with the following values for the columns RECORD_ID, AUDITOR_ID, PACKER_ID, SHIPPER_ID

RECORD_ID   AUDITOR_ID   PACKER_ID   SHIPPER_ID
0           0            1           3
1           1            2           3
2           3            1           2

I can write the following query to get the list of audits with the auditor name substituted for the AUDITOR_ID:

SELECT 
   emp.NAME,
   aud.PACKER_ID,
   aud.SHIPPER_ID
FROM
   ICQA_AUDITS aud, DB_EMPLOYEE emp
WHERE
   aud.AUDITOR_ID = emp.EMPID

The formatted output would look something like this:

RECORD_ID   NAME   PACKER_ID   SHIPPER_ID
0           Bob    1           3
1           Joe    2           3
2           Wendy  1           2

My question is this: How might I also get the PACKER_ID and SHIPPER_ID replaced by the appropriate names? Desired output would be the following:

RECORD_ID   NAME   PACKER_ID   SHIPPER_ID
0           Bob    Joe         Wendy
1           Joe    Carl        Wendy
2           Wendy  Joe         Carl

I'm trying to format a report region in APEX and I'm not seeing the easy way to do this. Any help will be much appreciated.

Thanks!

Edit

Expanded example: Suppose that instead of just having one NAME attribute, the DB_EMPLOYEE table has FIRSTNAME and LASTNAME, as follows:

EMPID   FIRSTNAME   LASTNAME
0       Bob         Bobbington
1       Joe         Josephson
2       Carl        Carlton
3       Wendy       Van Dorfenstein

I can write the example query as:

SELECT 
   emp.LASTNAME || ', ' || emp.FIRSTNAME as "Auditor Name",
   aud.PACKER_ID,
   aud.SHIPPER_ID
FROM
   ICQA_AUDITS aud, DB_EMPLOYEE emp
WHERE
   aud.AUDITOR_ID = emp.EMPID

The output would then be:

RECORD_ID   Auditor Name            PACKER_ID   SHIPPER_ID
0           Bobbington, Bob         1           3
1           Josephson, Joe          2           3
2           Van Dorfenstein, Wendy  1           2

The desired output would then be:

Record   Auditor Name            Packer Name      Shipper Name
0        Bobbington, Bob         Josephson, Joe   Van Dorfenstein, Wendy
1        Josephson, Joe          Carlton, Carl    Van Dorfenstein, Wendy
2        Van Dorfenstein, Wendy  Josephson, Joe   Carlton, Carl

Upvotes: 0

Views: 56

Answers (1)

winston86
winston86

Reputation: 159

Try this query:

  SELECT 
    a.RECORD_ID as id,   
    b.name as auditor_name, 
    c.name as packer_name, 
    d.name as shiper_name
   FROM ICQA_AUDITS a
    LEFT JOIN DB_EMPLOYEE b ON b.EMPID = a.AUDITOR_ID
    LEFT JOIN DB_EMPLOYEE c ON c.EMPID = a.PACKER_ID
    LEFT JOIN DB_EMPLOYEE d ON d.EMPID = a.SHIPPER_ID
   WHERE a.RECORD_ID = 1

The response must be like this:

id auditor_name packer_name shiper_name
1  Joe          Carl        Wendy

Change "WHERE" statement to your needs. Good luck.

Upvotes: 2

Related Questions