How to reference field in a JOIN within a JOIN?

Question

When selecting a specific field within a subquery in a JOIN statement, you use dot notation to reference the table.field_name, however, what to do when using yet another subquery JOIN within that first JOIN?

My example of the JOIN w/in JOIN:

JOIN (SELECT 
BUDGET.protocol_id, BUDGET.completed_date,
CONTRACT.completed_date,
REQUEST.completed_date,
RECEIVE.completed_date,
PC.completed_date,
FC.completed_date,
MGR.completed_date

FROM (SELECT completed_date, task_list_id, protocol_id FROM task WHERE task_name LIKE 'Budget%') BUDGET 

JOIN (SELECT completed_date, task_list_id, protocol_id FROM task WHERE task_name LIKE 'Contract%') CONTRACT 
    ON BUDGET.protocol_id = CONTRACT.protocol_id
    AND BUDGET.task_list_id = CONTRACT.task_list_id

JOIN (SELECT completed_date, task_list_id, protocol_id FROM task WHERE task_name LIKE 'Request%') REQUEST 
    ON BUDGET.protocol_id = REQUEST.protocol_id
    AND BUDGET.task_list_id = REQUEST.task_list_id

JOIN (SELECT completed_date, task_list_id, protocol_id FROM task WHERE task_name LIKE 'Protocol%') PC 
    ON BUDGET.protocol_id = PC.protocol_id
    AND BUDGET.task_list_id = PC.task_list_id

JOIN (SELECT completed_date, task_list_id, protocol_id FROM task WHERE task_name LIKE 'Financials%') FC 
    ON BUDGET.protocol_id = FC.protocol_id
    AND BUDGET.task_list_id = FC.task_list_id

JOIN (SELECT completed_date, task_list_id, protocol_id FROM task WHERE task_name LIKE 'Manager%') MGR 
    ON BUDGET.protocol_id = MGR.protocol_id
    AND BUDGET.task_list_id = MGR.task_list_id  

JOIN (SELECT completed_date, task_list_id, protocol_id FROM task WHERE task_name LIKE 'Receive%') RECEIVE
    ON BUDGET.protocol_id = RECEIVE.protocol_id
    AND BUDGET.task_list_id = RECEIVE.task_list_id
) TASK ON PCL.protocol_id = TASK.BUDGET.protocol_id

What I've been trying to do with the subqueries is select the specific task completed dates, so in the SELECT statement my instinct is to use TASK.BUDGET.completed_date, for each task. Yet, I get an ORA-00918: column ambiguously defined, so there seems to be something wrong with everything using completed_date

Answer 1

Use conditional aggregation:

SELECT t.protocol_id,
       MAX(CASE WHEN task_name LIKE 'Budget%' THEN completed_date END) as budget_completed_date,
         . . . 
FROM task t
GROUP BY protocol_id;

I'm not quite sure how task_list_id fits in. You might also want to be aggregating by that.