Oracle SQL - Return modified records, problem with ordering when joining-CodePudding

I need to return rows for child p_id ( parent is p_id = 10 and child = 20) which were changed.

I'm using something like below in link, but its ordering incorrectly so I'm getting wrong results. It would be easier to explain on example:

All required data and queries here: db<>fiddle

As you can see in results, child and parent rows have mixed order (eg. first row: CHILD_DATE_FROM = 03/05/2022 and PARENT_DATE_FROM = 08/05/2022 (should be also 03/05/2022))

To clarify (as I cannot explain it better) for this data I need to return results like below (as only these rows have changed)

| pl_id | l_id  | date_from  | date_to          |
| ----- | ----- | ---------- | ---------------- |
| 20    | 313   | 10/05/2022 | 10/05/2022 18:21 |
| 20    | 316   | 11/05/2022 | 13/05/2022 04:22 |
| 20    | 316   | 15/05/2022 | 17/05/2022       |

Thanks for help

CodePudding user response：

I wasn't sure how to tell if a record was changed but looks like something like that?

select distinct c.*
from child_gtt c, parent_gtt p
where p.l_id = c.l_id 
and not exists (
  select 1 
  from parent_gtt x 
  where c.l_id = x.l_id 
  and c.date_from = x.date_from 
  and c.date_to = x.date_to);

db<>fiddle