[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.
thanks
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
SELECT ...
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
LEFT OUTER
JOIN ...
is this helpful?
More information about the thelist
mailing list