Sql – Get data rows for past 7 days using sql in Oracle

oracleoracle-sqldevelopersql

Struggling with the query which returns data rows including some rows for the past 7 days. There are lots of left joins tables and looks like I've missed something. here is an example of my code so far:

select a.a,
b.b,
c.c,
d.d,
e.e,
f.f,
s.start_date,
ack.acknowledge_date,
fin.finish_date, 
g.g,
h.h, 
i.i 
from table_a a, 
inner join table_b b on ... = ..., 
left join table_c c on ... = ..., 
left join table_d d on ... = ... ` 

And so on.

I would like to get data rows (if any!) from columns s.start_date, ack.acknowledge_date and
Fin.finish_date for the last 7 days (I suggest it is (sysdate -7)). However, if I do the following:

select a.a,
b.b,
c.c,
...,
...,
...,
from ...
inner join ...
on ... = ...
left join ...
on ... = ...
left join`...
on ... = ...

where s.start_date >= sysdate -7 and 
ack.acknowledge_date >= sysdate -7 and 
fin.finish_date >= sysdate -7;

Then I have 0 rows in returns. But ideally query should return all rows from all tables and IF any rows on these date fields it should return them too, if there is no data in these date fields then leave it empty but all other rows should return as normal.

Best Solution

Don't put OUTER table conditions in the WHERE clause if it's an OUTER JOIN. Move them to the ON clause instead!

LEFT JOIN <...> ON <...> = <...>
               AND s.start_date >= sysdate -7