Help (MULTI-JOINS) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help (MULTI-JOINS)

Author

Topic

SASSQL
Starting Member

2 Posts

Posted - 2003-02-25 : 15:19:58

Having a problem figuring this one out so any help would be appreciated.

Here is the query I have:

select f.cluster, c.order_class_description, sum(a.subscribers) as counts
from closed_order_summary a inner join period b
on a.period_key = b.period_key inner join order_class c
on a.order_class_key = c.order_class_key inner join order_status d
on a.order_status_key = d.order_status_key inner join product e
on a.product_key = e.product_key inner join system f
on a.system_key = f.system_key
where b.date between '2/22/2003' and '3/21/2003' and e.service_code in('BA','Q2')
and d.order_status_code in('C','D') and c.order_class_description <> 'Quantity Change'
group by f.cluster, c.order_class_description
order by f.cluster, c.order_class_description

My problem is I want every row from Order_Class(every Order_Class_Description) and every row from System(every Cluster) in my result set. I know how to use the LEFT/RIGHT Outer join for simple 2 table queries but can't figure out the way for this 6-way join.

Again, thanks for the help.

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-25 : 15:36:55

Let's see, you want everything from Order Class and System (Not a good tbname btw). I would guess that you need to make order class the "driver" of the query. How about this:

SELECT f.cluster, c.order_class_description, sum(a.subscribers) as counts
FROM order_class c
LEFT JOIN period b
ON a.period_key = b.period_key
LEFT JOIN closed_order_summary a
ON a.order_class_key = c.order_class_key
LEFT JOIN order_status d
ON a.order_status_key = d.order_status_key
LEFT JOIN product e
ON a.product_key = e.product_key
FULL JOIN system f
ON a.system_key = f.system_key
WHERE b.date BETWEEN '2/22/2003' AND '3/21/2003'
AND e.service_code in('BA','Q2')
AND d.order_status_code in('C','D')
AND c.order_class_description <> 'Quantity Change'
GROUP BY f.cluster, c.order_class_description
ORDER BY f.cluster, c.order_class_description

Let me know if it helps you

Brett

8-)

SASSQL
Starting Member

2 Posts

Posted - 2003-02-25 : 15:54:44

Thanks for the input X002548. I agree System is a terrible name... oh well.

I tried your code but it still didn't given me what I needed although I appreciate the suggestion. Basically the tables are like a star schema. The Closed_Order_Summary table is the fact table and the others are the dimensions as it were. Forgot to mention that and not sure if it helps anyhow.

Anyways, this is what I wrote to give me the results I wanted but is there a simple and more elegant, not to mention efficient, way to accomplish this?

select a.cluster, a.order_class_description, b.counts
from
(

select distinct a.cluster, b.order_class_Description
from system a cross join order_class b
) as a left outer join
(
select f.cluster, c.order_class_description, sum(a.subscribers) as counts
from order_class c inner join closed_order_summary a
on a.order_class_key = c.order_class_key inner join system f
on a.system_key = f.system_key
where a.period_key in(select period_key from period where date between '2/22/2003' and '3/21/2003')
and a.order_status_key in(select order_status_key from order_status where order_status_code in('C','D'))
and a.product_key in(select product_key from product where service_code in('BA','Q2'))
and c.order_class_description<>'Quantity Change'
group by f.cluster, c.order_class_description
) as b
on a.cluster=b.cluster and a.order_class_description=b.order_class_description
order by a.cluster, a.order_class_description

Again, appreciate the help.

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-25 : 16:07:30

Hey if it works, it WORKS. Then again, how many rows of data are we talking here, and what is the predicated growth. You mentioned that this is a warehouse? If it is, is this process part of a nightly batcjh to populate the warehouse with derived data? Or is it an OLTP DB. All these things determine if you need to alter it.

One suggestion I would have is to show the execution plan to see whats going on.

In any event good luck.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources