CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Luke
Luke

Reputation: 781

Oracle SQL [1..*] Relation | Select all rows from [1...] + top-row from [...*] after order by in [...*]

I want to select * from Table TRAINEE + the first REPORT.DATE within the last 2 months. 

TRAINEE
+----+----------+
| ID | NAME     |   
+----+----------+   
| 1  | John Doe |
+----+----------+
| 2  | Jane Doe |
+----+----------+

REPORT
+------------+------------+---------------+
| TRAINEE_ID | DATE       | REPORT        |
+------------+------------+---------------+
| 1          | 01.07.2018 | Not Important |
+------------+------------+---------------+
| 1          | 02.07.2018 | Not Important |
+------------+------------+---------------+
| 1          | 03.07.2018 | Not Important |
+------------+------------+---------------+
| 2          | 02.07.2018 | Not Important |
+------------+------------+---------------+
| 2          | 02.07.2018 | Not Important |
+------------+------------+---------------+
| 2          | 03.07.2018 | Not Important |
+------------+------------+---------------+


Wanted results
+----+----------+--------------+
| ID | NAME     | FIRST_REPORT |
+----+----------+--------------+
| 1  | John Doe | 01.07.2018   |
+----+----------+--------------+
| 2  | Jane Doe | 02.07.2018   |
+----+----------+--------------+

I have tried...

This way gave me only one row with a date, rest filled with NULL, because the join only returns one row. Removing the ROWNUM filter will make my query return one row of TRAINEE for each row found in REPORT. So this wouldn't work as well. Where do I have to put the ROWNUM filter?

SELECT 
    TRAINEE.*, 
    OUTER_ORDER_DATE.DATE 
FROM
    Trainee
LEFT JOIN 
    (
    SELECT 
        DATE,
        ID,
        ROWNUM as rnum
    FROM 
        (
            SELECT 
                 DATE,
                 ID, 
              FROM 
                 REPORT INNER_ORDER_DATE 
             WHERE 
                 INNER_ORDER_DATE.DATE >= add_months(sysdate,-2)
             ORDER BY 
                 INNER_ORDER_DATE.DATE ASC
                                 )  
    WHERE 
        rnum < 2
        ) ON OUTER_ORDER_DATE ON OUTER_ORDER_DATE.ID = a.ID

I then tried the following query, that has some wrong syntax; the inner query cannot access the outer query's Trainee.ID.

SELECT 
    Trainee.*,
    (SELECT 
        DATE 
       FROM (
          SELECT 
               DATE 
            FROM 
               REPORT 
           WHERE
               ID = TRAINEE.ID 
             AND 
               DATE >= add_months(sysdate,-2)
           ORDER BY
               DATE ASC
                  ) 
      WHERE
          ROWNUM < 2
     ) as DATE
 FROM 
    TRAINEE

What does my query has to look like, to get the wanted result above?

If this question was answered already please link it for me. I have no idea how I could search for this scenario. Thank you.

Upvotes: 3

Views: 54

Answers (2)

davegreen100
davegreen100

Reputation: 2115

Try this:

select t.id, t.name, min(r.date) firstdate
from trainee t, report r
where t.id = r.id
and r.date >= add_months(sysdate,-2)

Upvotes: 0

Goran Kutlaca
Goran Kutlaca

Reputation: 2024

You should try using this code:

SELECT trainee.id, trainee.name, report.report_date
  FROM trainee
       JOIN (  SELECT trainee_id, MIN (report_date) AS report_date
                 FROM report
                WHERE report_date >= ADD_MONTHS (SYSDATE, -2)
             GROUP BY trainee_id) report
           ON (report.trainee_id = trainee.id)

Upvotes: 3

Related Questions