outer join problem - sql 92 syntax
-
I'm having problems converting an old outer join query to sql 92 syntax.
my existing query below runs ok, returning the values I want
FROM
day_tmp sw,
day_store_keys st,
day_keys w ,
day_hier_keys h,
database.dbo.sclass_struct sc
where
st.store_key *= sw.store_key
and w.fin_week_key *= sw.fin_week_key
and w.day_key *= sw.day_key
and h.fin_form_key *= sw.fin_form_key
and sc.sclass_key *= sw.sclass_key
and h.sdept_key = sc.sdept_key
however if I run;
FROM
day_tmp sw
right outer join day_store_keys as st on st.store_key = sw.store_key
right outer join day_keys as w on w.fin_week_key = sw.fin_week_key
and w.day_key = sw.day_key
right outer join day_hier_keys as h on h.fin_form_key = sw.fin_form_key
right outer join database.dbo.sclass_struct as sc on sc.sclass_key = sw.sclass_key
AND h.sdept_key = sc.sdept_key
OR if I run;
FROM
day_tmp sw
right outer join day_store_keys as st on st.store_key = sw.store_key
right outer join day_keys as w on w.fin_week_key = sw.fin_week_key
and w.day_key = sw.day_key
right outer join day_hier_keys as h on h.fin_form_key = sw.fin_form_key
right outer join database.dbo.sclass_struct as sc on sc.sclass_key = sw.sclass_key
WHERE h.sdept_key = sc.sdept_key
then I can't replicate the results from my original query. I can see from the execution plans that all 3 are being interpreted differently, just confused as to how the syntax is working.
any help appreciated.
-
One remark I would like to make (from BOL) is that the order in which you specify the tables IS important when using LEFT or RIGHT inner joins.
I did not have a look at your specific query, but I guess that might explain the differences.
In your last query, you are using a 'old style' inner join syntax, whereas in the other SQL92 syntax you are not. That probably explains any difference between those two queries.
-
Try simplifying the problem by joining two tables in both ways and seeing if there is a divergence, then three tables and so on.
Without data it is difficult to debug this but I would suggest the connection to the day_keys table may be the problem because of the multiplication of result rows that outer joining to this table twice will cause.
-
Here goes nothing. I started from your first alternative solution, and changed / corrected the 'inner join' problem as I stated in my previous post.
I don't know if this solves all of your problems, but it's worth a try...
FROM
day_tmp sw
right outer join
day_store_keys as st
on st.store_key = sw.store_key
right outer join
day_keys as w
on w.fin_week_key = sw.fin_week_key
and w.day_key = sw.day_key
right outer join
day_hier_keys as h
INNER JOIN database.dbo.sclass_struct as sc ON h.sdept_key = sc.sdept_key
on h.fin_form_key = sw.fin_form_key
AND sc.sclass_key = sw.sclass_keyJust doing this on the site, so I did not test it. As amelvin stated, it would be good to get a (small
) dataset, that clearly shows the divergence between the different solutions. -
Thanks for your help, sorry for the delay in getting back to you.
Tried your suggestions and re-ordering joins but no luck.
what I'm trying to do is create a fact table with all possible hierachy combos even if there are zero sales(i.e no values in sales table) for use in data warehouse app.
I create rows for all days, all products and all stores regardless of whether the have sales values. With old syntax it was realtively straight forward, you just creat the necessary outer joins (4 in this case) with the sales table as the subservient table. However with the SQL 92 syntax I can't seem to replicate this.
The select statement for above from clauses is below
SELECT
h.sdept_key,
h.fin_form_key,
w.day_key,
w.fin_week_key,
w.fin_week_comm,
w.fin_month_key,
w.fin_month_no,
w.fin_year_key,
st.store_key,
sum(isnull(sw.retail_value,0)),
sum(isnull(sw.local_value,0))
-
SELECT
h.sdept_key,
h.fin_form_key,
w.day_key,
w.fin_week_key,
w.fin_week_comm,
w.fin_month_key,
w.fin_month_no,
w.fin_year_key,
st.store_key,
sum(isnull(sw.retail_value,0)),
sum(isnull(sw.local_value,0))
FROM
day_hier_keys h
inner join database.dbo.sclass_struct sc
on sc.sdept_key = h.sdept_key
cross join day_store_keys st
cross join day_keys w
left outer join day_tmp sw
on sw.store_key = st.store_key
and sw.fin_week_key = w.fin_week_key
and sw.day_key = w.day_key
and sw.fin_form_key = h.fin_form_key
and sw.sclass_key = sc.sclass_key
Edited by - davidburrows on 05/28/2003 03:29:17 AM
Far away is close at hand in the images of elsewhere.
Anon. -
Trying to build it from scratch :
1. Start from the DAY table that should contain an entry for each day.
2. Adding the STORE and PRODUCT table. You want to have one record for each store and each product and each day. Seems like a cross join. Just be carefull that when you add a WHERE clause, the cross join acts like an inner join !
3. Join this to the SALE table, with a zero if no record exists...
SELECT * FROM
DAYS CROSS JOIN STORE
CROSS JOIN PRODUCT
LEFT OUTER JOIN SALE
ON DAYS.Day_ID = SALE.Day_ID
AND STORE.Store_ID = SALE.Store_ID
AND PRODUCT.Product_ID = SALE.Product_IDOf course, you will have to fill in your own field names and stuff...
-
Cheers, all sorted. I'd got so bogged down in trying to make the outer syntax work I hadn't even thought about using cross joins.
Once again, thanks.
) dataset, that clearly shows the divergence between the different solutions.Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply