Calling Mr. Cross Join - 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

SQL Server Development (2000)

Calling Mr. Cross Join

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-05-11 : 16:49:21

Jeff or anyone else who might have an answer,
I have a problem similar to the one I encountered in the last topic you helped me with, but for some reason the results being produced aren't as expected. I have a table with contracts and their statuses, I need to pull a report for a specified date range of the number of each status that occurred during that period, broken down by partner. I figured the cross join would give me what I wanted, except it either multiplies the Count() by 5(the number of possible statuses), and doesn't even show all statuses, or it gets the count of all records for the partner for each status, instead of the number of records per status, per partner. Below is a run down version of the query I'm using.

Select
C.PartnerName,
CAS.ContractAcceptanceStatus,
Count(C.ContractAcceptanceStatusID)
From
vwContracts C
Cross Join tblContractAcceptanceStatuses CAS
Group By
C.PartnerName,
CAS.ContractAcceptanceStatus
Order By
C.PartnerName

If I group by and use CAS.ContractAcceptanceStatus, then I get the number of records per partner for each status. If I group by and use C.ContractAcceptanceStatus, then I get the counts multiplied by 5, but not all statuses per partner.

Thanks,
Steve

Edited by - blastrix on 05/11/2003 17:05:46

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-11 : 18:27:23

Close ... you almost always need to use subqueries when using these techniques.

Think of it in two pieces:

Part 1: You want to return a row for EVERY partner, and for EVERY acceptance type:

SELECT P.PartnerName, CAS.ContractAcceptanceStatus
FROM
Partners P (you do have this table, right?)
CROSS JOIN
tblContractAcceptanceStatuses CAS

(Replace the "Partners" table with either the master table of partners, or a subquery that returns 1 row per partner you wish to show on this report)

Part 2: Totally seperate from SQL1, create a SQL statement that returns the numbers you want to show each partner/status combination. Make sure the "primary key" of this query is partner/status:

Select C.PartnerName, C.ContractAcceptanceStatus, COUNT(*) as StatusCount
FROM
vwContracts C
GROUP BY PartnerName, ContractAcceptanceStatus

(you may need to edit the above based on your data, but I imagine it is something like that).

Part 3: (Final Result)

Now, just do a left outer join from part 1 to part 2:

Select a.partnerName, a.ContractAcceptanceStatus
isnull(b.StatusCount,0) as StatusCount
FROM
(Part 1) a
LEFT OUTER JOIN
(part 2) b
ON
a.PartnerName = b.PartnerName AND
a.ContractAcceptanceStatus = b.ContractAcceptanceStatus

----
I hope this makes some sense and clears things up. You have to do it in steps. The CROSS JOIN is only for ensuring you have EVERY combination that you need. If you don't need a row for EVERY partner and for EVERY status, don't bother with it. It is just a way to guarantee you return a row for every combination (and allows you a chance to return 0's when there is no data).

Same thing if you said: "Give me sales per month, per region, per product -- and show a 0 if there are no sales":

Select A.Month, B.Region. C.Product, ISNULL(D.TotalSales,0) as TotalSales
FROM
Months A
CROSS JOIN
Regions B
CROSS JOIN
Products C
LEFT OUTER JOIN
(SELECT month, region, product, sum(Sales) as TotalSales FROM Salesdata GROUP BY month, region, product) D
on
A.Month = d.Month AND
B.Region = d.Region AND
C.Product = d.Product

I call this the (A x B) -> C technique. You cross join A and B to make you you have all of the dimensions your query needs to return (regardless of actual data), and then a LEFT OUTER JOIN to C which is the actual data properly summarized.

Trust me on this: if you can master this technique, you will be the ultimate SQL query writer (especially for writing reports). Things that aren't normally possible to return (or very inefficient to return) become very straightfoward and make perfect logical sense when you apply this method.

- Jeff

Edited by - jsmith8858 on 05/11/2003 18:30:13

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources