Cartesian product!? - 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 2005 Forums

Transact-SQL (2005)

Cartesian product!?

Author

Topic

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2007-11-17 : 08:02:08

I am working on some kind of requirement and here is what I need to do.

I have two tables

TabA
Col11 Col1 Col2
A 1 2
A 1 3
A 2 4

TabB
Col11 Col3 Col4
A 11 21
A 11 31
A 12 41

I would need results as

1 2 11 21
1 2 11 31
1 2 12 41
1 3 11 21
1 3 11 31
1 3 12 41
2 4 11 21
2 4 11 31
2 4 12 41

Alright, this looks simple when thought. It is like multiplication, and I could do it with "dreaded cartesian product" CROSS JOIN. This is crazy stuff. I am looking at tables that could have anywhere from 1 - 1 million rows in each table. It is just all possible combinations should be given in the resultset for particular value A.

Here was my query

select x.col1, x.col2, y.col3, y.col4
from TabA as x
cross join
TabB as Y
where x.col11 = 'A' and y.col11 = 'A'

Can someone help me in finding a way out without using CROSS JOIN?

Thanks in advance!

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-17 : 08:17:50

If those are the results you want, a CROSS JOIN is exactly what you should use. I am not sure what your problem is -- do you want a cartesian product or not?

- Jeff
http://weblogs.sqlteam.com/JeffS

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-17 : 08:20:59

This will give you the same result using INNER JOIN

select	Col1, Col2, Col3, Col4
from	TabA a inner join TabB b
	on	a.Col11	= b.Col11
where   a.Col11 = 'A'

What you want basically is a result of cross join between 2 tables. Don't think the is any other way doing in.

The question is do you really need to return 1 mil x 1 mil back to the user ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources