Thanks,
TreyThis is what I am trying to do:
SELECT DISTINCT dbo.[table].*
FROM dbo.x INNER JOIN
dbo.y ON dbo.x.id = dbo.y.id
WHERE (dbo.y.reason_id = '4744')
Intersect
SELECT DISTINCT dbo.[table].*
FROM dbo.x INNER JOIN
dbo.y ON dbo.x.id = dbo.y.id
WHERE (dbo.y.reason_id <> '4786')
Hi Everyone,
We are facing some problem in the cube particularly in INTERSECT function.
Here are the details.
Dimension Tables:
DimTime 200601
200602
DimProduct 01
02
DimUser 101
102
103
FactTables:
FactPlayer
Time Product User
200601 01 101
200601 02 101
200601 01 102
Transact SQL Query:
Select Count(*) from
(
Select userid from FactPlayer where productId = 01
INTERSECT
Select userid from FactPlayer where productId= 02
)
PlayerCount
We want the same result from MDX query. Can you please guide us how to do it by using INTERSECT.
Expecting your valuable reply.
Regards
Vijay
Hi Vijay,
You could solve this using the Intersect function, but you don't need to. Here's an example from Adventure Works showing all the Customers who bought products from two different subcategories (mountain bikes and caps):
select {[Measures].[Internet Sales Amount]} on 0,
nonempty(
nonempty(
[Customer].[Customer].[Customer].members,
([Measures].[Internet Sales Amount], [Product].[Subcategory].&[1])
)
, ([Measures].[Internet Sales Amount],[Product].[Subcategory].&[19])
)
on 1
from [Adventure Works]
What it's doing is using the nonempty function to return a list of Customers who bought products in subcategory 1, and then using another nonempty function to filter that list by those who bought products from subcategory 19. This, I think, will be more efficient than using the Intersect function although for the record here's the same query rewritten to use Intersect:
select {[Measures].[Internet Sales Amount]} on 0,
intersect(
nonempty(
[Customer].[Customer].[Customer].members,
([Measures].[Internet Sales Amount], [Product].[Subcategory].&[1])
)
,nonempty(
[Customer].[Customer].[Customer].members,
([Measures].[Internet Sales Amount],[Product].[Subcategory].&[19])
)
)
on 1
from [Adventure Works]
HTH,
Chris
|||Hi Chris,
Thank you very much. It is working perfectly.
Vijay
|||
Hi Everyone,
We are facing some problem in the cube particularly in INTERSECT function.
Here are the details.
Dimension Tables:
DimTime 200601
200602
DimProduct 01
02
03
DimUser 101
102
FactTables:
FactPlayer
Time Product User
200601 01 101
200601 02 101
200601 01 102
200601 03 101
Transact SQL Query:
Select Count(*) from
(
Select user from FactPlayer where productId = 01
INTERSECT
Select user from FactPlayer where productId= 02
INTERSECT
Select user from FactPlayer where productId= 03
)
PlayerCount
RESULT: 1 (UserId: 101)
We want the same result from MDX query. Can you please guide us how to do it by using INTERSECT.
Expecting your valuable reply.
Regards
Vijay
|||I found the solution below.
SELECT NON EMPTY{[Measures].[User ID Distinct Count]} ON COLUMNS,
INTERSECT
(
NONEMPTY
(
INTERSECT
(
NONEMPTY
(
[DIM USER].[DIM USER].CHILDREN,
([Dim Time].[TimeKey].&[200602],
[Measures].[User ID Distinct Count],
[DIM PRODUCT].[DIM PRODUCT].&[3])
),
NONEMPTY
(
[DIM USER].[DIM USER].CHILDREN,
([Dim Time].[TimeKey].&[200602],
[Measures].[User ID Distinct Count],
[DIM PRODUCT].[DIM PRODUCT].&[11])
)
)
),
NONEMPTY
(
[DIM USER].[DIM USER].CHILDREN,
([Dim Time].[TimeKey].&[200602],
[Measures].[User ID Distinct Count],
[DIM PRODUCT].[DIM PRODUCT].&[12])
)
)
ON ROWS
FROM [DSV KPI]
Please reply me if there any changes in the query.
Thank You
Vijay
I have 6 different querys returning me this results
select * from threeLeptonCut;
select * from zVetoCut;
select * from topcut;
select * from jetVetoCut;
select * from leptoncuts;
select * from Misseecuts;
2 24485 14,7936 -310,021 bkg*.root
6 24416 31,2464 10,4272 bkg*.root
11 24411 22,04 -17,4846 bkg*.root
....
-
2 24485 14,7936 -310,021 bkg*.root
10 24461 75,7698 -1,09335 bkg*.root
13 24434 89,0862 -65,6022 bkg*.root
...
etc.
now i want to join the show all the tuples the appear in all 6 query results.
What do you think is the fastest way or lower time cost to do it?
Intersect, in, exists?, any other better?
On SQL Server 2005 INTERSECT operator is best approach,
Code Snippet
Select * From threeLeptonCut
Intersect
Select * From zVetoCut
Intersect
Select * From topcut
Intersect
Select * From jetVetoCut
Intersect
Select * From leptoncuts
Intersect
Select * From Misseecuts;
On SQL Server 2000 EXISTS is one of the best method. But for your query you have to check all the columns.
Code Snippet
Select * From threeLeptonCut T1
Exists (Select * From zVetoCut T2 Where T1.Col1=T2.Col1 And T1.Col2=T2.Col2 And T1.Col3=T2.Col3 And T1.Col4=T2.Col4)
And Exists (Select * From topcut T3 Where T1.Col1=T3.Col1 And T1.Col2=T3.Col2 And T1.Col3=T3.Col3 And T1.Col4=T3.Col4)
And Exists (Select * From jetVetoCut T4 Where T1.Col1=T4.Col1 And T1.Col2=T4.Col2 And T1.Col3=T4.Col3 And T1.Col4=T4.Col4)
And Exists (Select * From leptoncuts T5 Where T1.Col1=T5.Col1 And T1.Col2=T5.Col2 And T1.Col3=T5.Col3 And T1.Col4=T5.Col4)
And Exists (Select * From Misseecuts T6 Where T1.Col1=T6.Col1 And T1.Col2=T6.Col2 And T1.Col3=T6.Col3 And T1.Col4=T6.Col4)
|||I had just logged on to post a similar query to Luis. From his description I think he is (as I am) looking for the Intersection of the six queries. I have 4 tables, each containing 1 int column, and I want a list of integers which appear in all 4 tables only.Intersect only seems to work with two sets, not four.
Any ideas much appreciated.
Greg.
|||
You can use INTERSET on multiple sets..
Here the sample,
Code Snippet
Create Table #data1 (
[Data] Varchar(100)
);
Insert Into #data1 Values(1);
Insert Into #data1 Values(2);
Insert Into #data1 Values(3);
Insert Into #data1 Values(4);
Insert Into #data1 Values(5);
Create Table #data2 (
[Data] Varchar(100)
);
Insert Into #data2 Values(2);
Insert Into #data2 Values(30);
Insert Into #data2 Values(4);
Create Table #data3 (
[Data] Varchar(100)
);
Insert Into #data3 Values(2);
Insert Into #data3 Values(30);
Insert Into #data3 Values(4);
--SQL Server 2005
Select * From #Data1
Intersect
Select * From #Data2
Intersect
Select * From #Data3
--SQL Server 2000
Select * From #Data1 Where
Exists (Select 1 From #Data2 Where #Data1.Data = #Data2.Data)
And Exists (Select 1 From #Data3 Where #Data1.Data = #Data3.Data)
|||Thanks Manivannan. I've just realised that - need to get my "set-based" hat on again!.
Greg.
|||
Did you mean to say UNION instead of UNION ALL? he only wants the common rows, right?
|||phdiwakar thanks for pointing that .. I really surprised about this.. I edited now.
|||Hi Manivannan.D.Sekaran,
The OP is asking about comparison among different methods and for methods not mentioned, not how to do it using the methods mentioned in the list. Beside that, if you want to simulate the INTERSECT operator using EXISTS, then you need to use "select distinct" and also check for NULL values.
Example:
declare @.t1 table(c1 int)
declare @.t2 table(c1 int)
insertinto @.t1 values(1)
insertinto @.t1 values(1)
insertinto @.t1 values(null)
insertinto @.t2 values(2)
insertinto @.t2 values(null)
insertinto @.t2 values(1)
select c1 from @.t1
INTERSECT
select c1 from @.t2
selectdistinct
c1
from
@.t1 as t1
where
exists(
select*
from @.t2 as t2
where t2.c1 = t1.c1 or(t1.c1 isnulland t2.c1 isnull)
)
select
c1
from
@.t1 as t1
where
exists(
select*
from @.t2 as t2
where t2.c1 = t1.c1
)
go
AMB
