Hello experts;
Just curious, I have a very complicated query similar to the following sample code below and I was just wondering if there is an another way to re-write this query. Please see sample code below
with t as
(
select 1 as id, to_date('2019-10-11', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual
union all
select 2 as id, to_date('2019-10-12', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual
union all
select 3 as id, to_date('2019-10-15', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual
union all
select 4 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'Toronto' as place from dual
)
, t_others as
(
select 1 as id, to_date('2019-10-12', 'YYYY-MM-DD') as lastupdated, 'Blue' as color, 'Zone 7' as zoneid from dual
union all
select 2 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'Red' as color, 'Zone 7' as zoneid from dual
union all
select 3 as id, to_date('2019-10-16', 'YYYY-MM-DD') as lastupdated, 'White' as color, 'Zone 7' as zoneid from dual
union all
select 4 as id, to_date('2019-10-17', 'YYYY-MM-DD') as lastupdated, 'Green' as color, 'Zone 7' as zoneid from dual
)
,t_further_info as
(
select 1 as id, to_date('2019-10-12', 'YYYY-MM-DD') as lastupdated, 'A' as typeinfo from dual
union all
select 2 as id, to_date('2019-10-14', 'YYYY-MM-DD') as lastupdated, 'C' as typeinfo from dual
union all
select 3 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'D' as typeinfo from dual
union all
select 4 as id, to_date('2019-10-21', 'YYYY-MM-DD') as lastupdated, 'E' as typeinfo from dual
)
select * from t
inner join t_others pt
on t.id = pt.id
inner join t_further_info fi
on t.id = fi.id
where (t.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')
or pt.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')
or fi.lastupdated >= to_date('2019-10-21', 'YYYY-MM-DD')
)