Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Why can't you modify the FROM clause? Enforced by Hibernate/an ORM? Can you modify the SELECT clause? –  FreshPhilOfSO Apr 18 '12 at 15:41
2  
Can't you move the CHILD filters to the ON? Otherwise you are actually telling it to convert it to an inner join. –  Aaron Bertrand Apr 18 '12 at 16:49
3  
"Because of the technical setup" - the perils of ORM frameworks... –  a_horse_with_no_name Apr 18 '12 at 18:24
2  
I would change the technical setup - at the very least you should be able to invoke a stored procedure from your client. If you are using an imperfect ORM, you may end up spending a huge time working around issues likes this one. As such, you might be better off with a better technical setup. –  AlexKuznetsov Apr 18 '12 at 19:01
add comment

2 Answers

That one caused me quite a bit of headache before I found a halfway elegant solution:

WITH x AS (
    SELECT BASE.id AS b_id
          ,BASE.start_date AS b_start_date
          ,BASE.end_date AS b_end_date
          ,CHILD.id AS c_id
          ,CHILD.start_date AS c_start_date
          ,CHILD.end_date AS c_end_date
          ,CASE WHEN DATE '2011-1-1' BETWEEN CHILD.start_date AND CHILD.end_date
                                     OR CHILD.id IS NULL THEN 0 ELSE 1 END AS c_hit
    FROM   BASE LEFT OUTER JOIN CHILD ON (BASE.ID=CHILD.ID)
    WHERE  BASE.ID = 1
    AND    DATE '2011-1-1' BETWEEN BASE.start_date AND BASE.end_date
    )
SELECT b_id, b_start_date, b_end_date
      ,c_id, c_start_date, c_end_date
FROM   x
WHERE  x.c_hit = 0

UNION ALL
SELECT b_id, b_start_date, b_end_date
      ,NULL, NULL,         NULL
FROM   x
GROUP  BY b_id, b_start_date, b_end_date
HAVING min(c_hit) = 1;

Of course, the better solution would be to get rid of the restrictions and adjust the JOIN in the base query. As @a_horse expressed it so poetically in the comments above:

the perils of ORM frameworks

Anyway, to solve the question at hand:
After selecting rows where BASE.id and BASE.start_date / BASE.end_date match, we have four different cases:

  1. No child at all, so CHILD.id is NULL.
  2. Child matches.
  3. Child does not match and no other matching child.
  4. Child does not match but other matching child.

This is hard to solve on one query level. You could do it with window functions and CASE statements and DISTINCT, in fact I had a basic working query, but it gets monstrous.

Using a CTE dramatically simplifies the syntax.
In the CTE:

  • Filter BASE on id and date range. Still includes rows with non-matching children.
  • Assign aliases for identical column names
  • Tag rows where child matches the date

In the final SELECT:

  • Return all rows with matching children
  • Return one row where no matching children exist, with NULLs for child-columns.

Here is working demo on sqlfiddle.com for those who want to toy with it.

share|improve this answer
add comment

Edited suggestion:

Without changing your join conditions, perhaps you can null out your child fields in the select clause if they don't meet the criteria?:

Select base.*, 
case 
    when child.start_date <= to_date('1/1/2011','dd/mm/yyyy')
       and child.end_date > to_date('1/1/2011','dd/mm/yyyy')
    then child.field1
    else null
end as c_field1
from (...)
where base.start_date (...) and base.end_date (...) 

(no child.start_date or end_date in the where clause)

share|improve this answer
    
I initially thought this, but it's not that simple. The OUTER JOIN on the ID column (given the above test scenario) will do a successful join on the ID columns with the value 1 (so CHILD values wouldn't be NULL), then filter all of the rows out completely because of the WHERE clause restrictions. –  FreshPhilOfSO Apr 18 '12 at 15:26
    
Oh, I see! The problem is not that there are no child rows in the join, but that the joined child record doesn't qualify later on. –  Hellion Apr 18 '12 at 18:09
    
This will return all rows with not-matching children, where only one should be returned, and only if there are no matching children. –  Erwin Brandstetter Apr 22 '12 at 22:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.