CTE math problem - 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

General SQL Server Forums

New to SQL Server Programming

CTE math problem

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2010-05-27 : 18:14:11

I know there's a mathematical formula which will help me devise my query structure, but since I'm (too) ignorant of math, I'm hoping someone can point the way.

I have four tables.

1. Intersect combinations of two (each with each). For example,


With A_B(ID) As
(
Select ID
From T..A

Intersect

Select ID
From T..B
)

Select Count(ID) As A_B
From A_B

---

With A_C(ID) As
(
Select ID
From T..A

Intersect

Select ID
From T..C
)

Select Count(ID) As A_C
From A_C

---

<snip>

2. Intersect combinations of three.

3. Intersect combination of four (obviously one query, as I see it)

It seems like if I work from a formula and map from that, I won't have to dumbly sketch diagrams, draw links and so forth.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-27 : 21:12:20

sorry i cant understand what you're asking for? are you asking for a way to take intersect between two CTE?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dmilam
Posting Yak Master

185 Posts

Posted - 2010-06-01 : 16:35:36

Thanks; in other words, given 4 items, how many sets exist? 4*3*2*1 = 24? This doesn't seem right.

My sketch for Tables A,B,C,D

Doubled:
A,B. B,C. C,D.
A,C. B,D.
A,D.

Tripled:

A,B,C. B,C,D.
A,B,D.
A,C.D.

Quadrupled:

A,B,C,D.

How many CTEs are there using intersect (or any set operator)? (3*2*1) + (3*1) + 1 = 10

Correct? I know it seems really academic, but I want to think more mathematically.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-01 : 17:02:03

It depends if you are talking about combinations or permutations.

Tables A, B, C and D has one combination; A-B-C-D.
It has 6 permuations if A-B is the same as B-A.

If you know factorial calculation (4! equals 1*2*3*4 = 24), the formula is

(#Tables)! / (#Tuplets)! / (#Tables - #Tuplets)!

4! / 2! / 2! equals 24 / 2 / 2 = 6
4! / 3! / 1! equals 24 / 6 / 1 = 4
4! / 4! / 0! equals 24 / 24 / 1 = 1

N 56°04'39.26"
E 12°55'05.63"

dmilam
Posting Yak Master

185 Posts

Posted - 2010-06-01 : 18:17:43

Permutations, I suppose, since A-B-C-D in my sets = B-C-D-A and so on. I do not know factorial calculation, but it looks to be what I need. Thanks, Peso!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources