Oracle SQL - return multiple columns from subquery

Question

Let's take a simple query in Oracle:

SELECT
    CASE.ID,
    CASE.TYPE,
    CASE.DATE_RAISED
FROM
    CASE
WHERE
    CASE.DATE_RAISED > '2019-01-01'

Now let's say another table, EVENT, contains multiple events which may be associated with each case (linked via EVENT.CASE_ID). OR not exist at all. I want to report on the earliest-dated future event per case - or if nothing exists, return NULL. I can do this with a subquery in the SELECT clause, as follows:

SELECT
    CASE.ID,
    CASE.TYPE,
    CASE.DATE_RAISED,
    (
    SELECT
        MIN(EVENT.DATE)
    FROM
        EVENT
    WHERE
        EVENT.CASE_ID = CASE.ID
        AND EVENT.DATE >= CURRENT_DATE
    ) AS MIN_EVENT_DATE
FROM
    CASE
WHERE
    CASE.DATE_RAISED > '2019-01-01'

This will return a table like this:

Case ID Case Type   Date Raised Min Event Date
76      A           03/01/2019  10/05/2019
43      B           02/02/2019  [NULL]
89      A           29/01/2019  08/07/2019
90      A           04/03/2019  [NULL]
102     C           15/04/2019  20/05/2019

Note that if there do not exist any Events which match the criteria, the line is still returned but without a value. This is because the subquery is in the SELECT clause. This works just fine.

My problem, however, is if I want to return more than one column from the EVENT table - while still at the same time preserving the possibility that there are no matching rows from the EVENT table. The above code only returns EVENT.DATE as the single subquery result, to ONE column of the main query. But what if I also want to return EVENT.ID, or EVENT.TYPE? While still allowing for them to be NULL (if no matching records from CASE are found)?

I suppose I could use multiple subqueries in the SELECT clause: each returning just ONE column. But this seems horribly inefficient, given that each subquery would be based on the same criteria (the minimum-dated EVENT whose CASE ID matches that of the main query; or NULL if no such events found).

I suspect some nifty joins would be the answer - although I'm struggling to understand which ones exactly.

Please note that the above examples are vastly simplified versions of my actual code, which already contains multiple joins in the "old style" Oracle format, eg:

WHERE
    CASE.ID(+) = EVENT.CASE_ID

There are reasons why this is so - therefore a request to anyone answering this, please would you demonstrate any solutions in this style of coding, as my SQL isn't far enough advanced to be able to re-factor the "newer" style joins into existing code.

Answer 1

You can use a join and window functions. For instance:

select c.*, e.*
from c left join
     (select e.*,
             row_number() over (partition by e.case_id order by e.date desc) as seqnum
      from events e
     ) e
     on e.case_id = c.id and e.seqnum = 1;
where c.date_raised > date '2019-01-01';  -- assuming the value is a date

Answer 2

Is this what you mean? I just rewrote Gordon's answer with old Oracle join syntax and your code style.

SELECT
    CASE.ID,
    CASE.TYPE,
    CASE.DATE_RAISED,
    MIN_E.DATE AS MIN_EVENT_DATE
FROM
    CASE,
    (SELECT EVENT.*,
        ROW_NUMBER() OVER (PARTITION BY EVENT.CASE_ID ORDER BY EVENT.DATE DESC) AS SEQNUM
        FROM
            EVENT
        WHERE 
            EVENT.DATE >= CURRENT_DATE
    ) MIN_E
WHERE
    CASE.DATE_RAISED > DATE '2019-01-01'
    AND MIN_E.CASE_ID (+) = CASE.ID
    AND MIN_E.SEQNUM (+) = 1;