[thelist] trouble w/ JOINs in an Oracle query

r937 rudy at r937.com
Thu Oct 1 10:12:51 CDT 2009

> Oh, and good tip on placing the most restricted table first in the FROM
> clause.


although in this case i might also have started with the customer and traced
my way through the table relationships that way

i've re-read your original question, and now that i think about it, the
year-months that are missing aren't going to pop magically out of thin air
without special effort

what i mean is, you can't pull missing data from the data itself, without
something to compare it to, to see which ones are missing

so the outer join concept is still correct, where the outer table will 
contain all year-months that you want reported

you can create this table out of thin air, as it were, with the use of a 
numbers table in a derived query

  FROM ( SELECT '2009-01-01' -- start date
                + INTERVAL n MONTH   AS sale_date
           FROM numbers
          WHERE n BETWEEN 0 AND 11 -- for 1 year
       ) sale_dates
  JOIN ...

is this helpful?

More information about the thelist mailing list