I have 2 tables:
BASE
is master and is linked with CHILD
via field ID
: Left outer Join / 1:N relation.
These 2 tables contain historised data, defined by 2 date-fields: start_date
and end_date
.
However the history in BASE
is independent from the history in CHILD
. E.g.:
BASE
1 1/1/2000 1/1/2010
1 1/1/2010 31/12/2012
CHILD
1 1/1/2000 31/12/2005
1 31/12/2005 1/1/2010
The goal:
For a given date, select the active record in both table and show them in 1 result-record.
Problem:
If there is no match in CHILD
(e.g. 1/1/2011), I get “no rows” whereas it should show the data from BASE
(and Null-values for the CHILD
-fields)
This is my query:
SELECT
BASE.*, CHILD.*
FROM
BASE LEFT OUTER JOIN CHILD ON (BASE.ID=CHILD.ID)
WHERE
BASE.ID = '1'
AND BASE.START_DATE <= TO_DATE('1/1/2011', 'DD/MM/YYYY')
AND BASE.END_DATE > TO_DATE('1/1/2011', 'DD/MM/YYYY')
AND CHILD.START_DATE <= TO_DATE('1/1/2011', 'DD/MM/YYYY')
AND CHILD.END_DATE > TO_DATE('1/1/2011', 'DD/MM/YYYY')
I know that my LEFT JOIN
will be converted to an INNER JOIN
because of CHILD
- columns in the WHERE
clause. I also tried the IS NULL
-trick that I found on this forum, but I’m still struggling with it.
Important remark:
Because of the technical setup, it is not possible to change the FROM
clause, so I’m forced to find a solution via the WHERE
clause …
Can you help?
CHILD
filters to theON
? Otherwise you are actually telling it to convert it to an inner join.