Can't get Cross Join functionality to work

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)

Can't get Cross Join functionality to work

Author

Topic

SMerrill
Posting Yak Master

206 Posts

Posted - 2002-11-13 : 17:50:52

I am having a problem yielding a result set with zero counts.
I am expecting the following result set:

Date        PatternID   K
----------- ----------- ---
2002-07-01  1           1
2002-07-01  2           1
2002-07-01  3           1
2002-07-02  1           0
2002-07-02  2           1
2002-07-02  3           0
2002-07-03  1           1
2002-07-03  2           0
2002-07-03  3           0

Here is my schema:

CREATE TABLE [dbo].[History] (
	[Date] [datetime] NOT NULL ,
	[PatternID] [int] NOT NULL 
) ON [PRIMARY]

CREATE TABLE [dbo].[MinimalPatternList] (
	[PatternID] [int] NOT NULL 
) ON [PRIMARY]

ALTER TABLE [dbo].[History] WITH NOCHECK ADD 
	CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED ([Date],[PatternID])
ALTER TABLE [dbo].[MinimalPatternList] WITH NOCHECK ADD 
	CONSTRAINT [PK_MinimalPatternList] PRIMARY KEY  CLUSTERED ([PatternID])

delete from history
delete from minimalpatternlist
insert into History (Date, PatternID) Values ('7/1/2002',1)
insert into History (Date, PatternID) Values ('7/1/2002',2)
insert into History (Date, PatternID) Values ('7/1/2002',3)
insert into History (Date, PatternID) Values ('7/2/2002',2)
insert into History (Date, PatternID) Values ('7/3/2002',1)
insert into MinimalPatternList (PatternID) Values (1)
insert into MinimalPatternList (PatternID) Values (3)
GO

CREATE VIEW v_CrossJoin AS SELECT History.Date, 
MinimalPatternList.PatternID, COUNT(*) AS K
FROM History CROSS JOIN MinimalPatternList
GROUP BY History.Date, MinimalPatternList.PatternID

No matter what kind of join I write, I cannot get zero counts in my results.
Given my level of experience with the relational model, I am embarrased.
I find this cross-join idea easier in Access.
Thanks for your help,

--SMerrill
Seattle, WA

burbakei
Yak Posting Veteran

80 Posts

Posted - 2002-11-13 : 20:05:50

you can use the following SELECT in your view

SELECT History.Date,
MinimalPatternList.PatternID, COUNT(*) AS K
FROM History CROSS JOIN MinimalPatternList
GROUP BY History.Date, MinimalPatternList.PatternID
UNION ALL
SELECT History.Date, History.PatternID, 0
FROM History
LEFT OUTER JOIN MinimalPatternList
ON History.PatternID = MinimalPatternList.PatternID
WHERE MinimalPatternList.PatternID IS NULL
GROUP BY History.Date, History.PatternID
ORDER BY History.Date

but you must expect the following result set:

Date PatternID K
-------------------------------------------- ----------- -----------
2002-07-01 00:00:00.000 1 3
2002-07-01 00:00:00.000 3 3
2002-07-01 00:00:00.000 2 0
2002-07-02 00:00:00.000 2 0
2002-07-02 00:00:00.000 3 1
2002-07-02 00:00:00.000 1 1
2002-07-03 00:00:00.000 1 1
2002-07-03 00:00:00.000 3 1

SMerrill
Posting Yak Master

206 Posts

Posted - 2002-11-18 : 17:22:06

The second half of your union properly answers the question,
"List all dates which contain non-required patterns."

But this does not answer my question, which is,
"List all days that do not contain the required patterns, and list the missing patterns for those dates."

Edited by - smerrill on 11/18/2002 17:55:45

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-18 : 18:18:30

I consider myself somewhat of a CROSS JOIN expert (I use them and love them!) here is what I think:

First, you need a table of all patterns, which you have. You also need a table of all dates:

(SELECT DISTINCT Date FROM History) As EachDate

will do the trick.

From there, you CROSS JOIN the Patterns table with the EachDate query, giving you all combinations.

But, we are not done. Encapsulate the CROSS JOIN in a subquery, and using that subquery you do a LEFT OUTER JOIN to your History table. A NULL value in the History table means you return a 0, otherwise a 1. All of your key data comes from the CROSS JOIN'd subquery.

SELECT AllCombos.Date, AllCombos.PatternID, CASE WHEN History.Date is NULL THEN 1 ELSE 0 END as K
FROM
(SELECT EachDate.Date, P.PatternID
FROM
MinimalPatternList P
CROSS JOIN
(SELECT DISTINCT Date FROM HISTORY) EachDate) AllCombos
LEFT OUTER JOIN
History
ON
History.Date = ALlCombos.Date AND
History.PAtternID = AllCombos.PAtternID

I call this an (A x B) -> C type report; you do a cross join of table A and B to guarantee you have all values, and then from there do a LEFT OUTER JOIN to your actual data. They key is that all of your sorts and grouping and everything comes from the (A x B) portion, and your numerical results (handling NULL's as zeroes or whatever you want) comes from the C table.

Edited by - jsmith8858 on 11/18/2002 18:20:14

SMerrill
Posting Yak Master

206 Posts

Posted - 2002-11-18 : 18:31:51

SQL Team:
(JSmith just needs to reverse (his/her) 1 and 0 in the CASE statement, and it works. I think it is more elegant than mine, and I'm inspired by it. My view comes up with two more records than (his/hers), but still doesn't come up with all of them.)

Thanks for your help one and all ...
Here is the answer I arrived at just before JSmith replied. I got this one myself, even though it nearly drove SQL nuts!

SELECT Date, PatternID, COUNT(*) AS [Count] FROM History
GROUP BY Date, PatternID

UNION ALL

SELECT AllReq.Date, AllReq.PatternID, 0 AS [Count]
FROM (SELECT AllDates.Date, MinimalPatternList.PatternID
      FROM (SELECT DISTINCT Date FROM History) AllDates
            CROSS JOIN MinimalPatternList
     ) AllRequirements
LEFT OUTER JOIN History h ON AllRequirements.Date      = h.Date
                         AND AllRequirements.PatternID = h.PatternID
WHERE (h.PatternID IS NULL)

The only record that is missing is 7/3 Pattern #2, which is not really necessary, as it is not required. (JSmith's answer leaves out 7/1 #2 and 7/2 #2, which aren't necessary either.)
This makes me academically wonder if there is an easier way, but I don't know relational algebra yet.

We don't need no steenkin' algebra . . . We've got sub-queries!

Thanks for your time, SQL Team! It was fun!

--SMerrill
Seattle, WA

Edited by - smerrill on 11/18/2002 19:07:51

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-18 : 19:12:12

I just realized that your MinimalPAttern table doesn't have the #2 ... that's why mine doesn't show it! I read your table defs quickly and thought it contained 1, 2 and 3.

In order for a query like this to work, you would need all of the records in at least 1 table. If you can't add #2 to the patterns table, you could alter my query and do it this way:

SELECT AllCombos.Date, AllCombos.PatternID, CASE WHEN History.Date is NULL THEN 0 ELSE 1 END as K
FROM
(SELECT EachDate.Date, P.PatternID
FROM
(SELECT DISTINCT PAtternID FROM History) P
CROSS JOIN
(SELECT DISTINCT Date FROM HISTORY) EachDate) AllCombos
LEFT OUTER JOIN
History
ON
History.Date = ALlCombos.Date AND
History.PAtternID = AllCombos.PAtternID

That will give you all combinations.

Thanks for the nice words. I'm a "he", by the way!

(and thanks for pointing out the error in my CASE clause ... oops !)

- Jeff

Edited by - jsmith8858 on 11/18/2002 19:18:24

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources