*Complex* Grouping in View - help needed

Hello,

I am having difficulty to find the right SQL query to create a View as i illustrate below.

Senario:

Criteria Table

Idn

Key1

Key2

Key3

TagId

1

A

C

B

100

2

A

NULL

B

200

3

B

D

NULL

300

Data Table

DataId

Key1

1

A

2

B

SubData Table

SubDataId

DataId

Key2

Key3

1

1

C

B

2

1

Z

B

3

1

X

B

4

2

D

Z

And below is my expected View:

TagId

Key1

Key2

Key3

100

A

C

B

200

A

Z

B

200

A

X

B

300

B

D

Z

I managed to get query that will be able to get above result, however it is teribbly slow. it took 1 minutes to query 1000 records from the view that i have created. My records are roughly around 80K++.

I would really appreciate if anyone could help me to make if faster or point me where i did wrong.

Below is sample T-SQL that which i can illustrate my situation:

set nocount on
create table #Criteria
(
idn int
,Key1 char(1)
,Key2 char(1)
,Key3 char(1)
,TagId int
)

create table #Data
(
DataId int
,Key1 char(1)
)

create table #SubData
(
SubDataId int
,DataId int
,Key2 char(1)
,Key3 char(1)
)

insert #Criteria (idn, Key1, Key2, Key3, TagId)
values(1, 'A', 'C', 'B', 100)
insert #Criteria (idn, Key1, Key2, Key3, TagId)
values(1, 'A', NULL, 'B', 200)
insert #Criteria (idn, Key1, Key2, Key3, TagId)
values(1, 'B', 'D', NULL, 300)

insert #Data(DataId, Key1)
values (1, 'A')
insert #Data(DataId, Key1)
values (2, 'B')

insert #SubData(SubDataId, DataId, Key2, Key3)
values (1, 1, 'C', 'B')
insert #SubData(SubDataId, DataId, Key2, Key3)
values (2, 1, 'Z', 'B')
insert #SubData(SubDataId, DataId, Key2, Key3)
values (3, 1, 'X', 'B')
insert #SubData(SubDataId, DataId, Key2, Key3)
values (4, 2, 'D', 'Z')

select #Data.Key1
,#SubData.Key2
,#SubData.Key3
from #Data
join #SubData
on #Data.DataId = #SubData.DataId

/** here is the query logic i used in the view **/
select min(#Criteria.TagId)
,ConsolidatedData.Key1
,ConsolidatedData.Key2
,ConsolidatedData.Key3
from #Criteria
left join ( select #Data.Key1
,#SubData.Key2
,#SubData.Key3
from #Data
join #SubData
on #Data.DataId = #SubData.DataId ) as ConsolidatedData
on nullif(#Criteria.Key1, ConsolidatedData.Key1) IS NULL
and nullif(#Criteria.Key2, ConsolidatedData.Key2) IS NULL
and nullif(#Criteria.Key3, ConsolidatedData.Key3) IS NULL
group by ConsolidatedData.Key1
,ConsolidatedData.Key2
,ConsolidatedData.Key3

drop table #Criteria, #Data, #SubData

P/s: i urgently need your feedback on this one.

Thank You!!!

sibikos@.hotmail.com

You posted a query but depending on you explanation maybe there's a completely different approach...

May you please explain which is the expected result ? something like "I would get a table that shows the key1 for each ...|||

Sorry for the confusion.. i always have trouble on explaining thing well :P

Basically, From the Criteria, Data and SubData tables that i've listed above, i would like to create a view as below

TagId

Key1

Key2

Key3

100

A

C

B

200

A

Z

B

200

A

X

B

300

B

D

Z

Currently i am using the query below query (well, not exactly the same but the logic is there) to get the view that i wanted but it is too slow for me. Just wondering if you guys have better solution.

select min(#Criteria.TagId)
,ConsolidatedData.Key1
,ConsolidatedData.Key2
,ConsolidatedData.Key3
from #Criteria
left join ( select #Data.Key1
,#SubData.Key2
,#SubData.Key3
from #Data
join #SubData
on #Data.DataId = #SubData.DataId ) as ConsolidatedData
on nullif(#Criteria.Key1, ConsolidatedData.Key1) IS NULL
and nullif(#Criteria.Key2, ConsolidatedData.Key2) IS NULL
and nullif(#Criteria.Key3, ConsolidatedData.Key3) IS NULL
group by ConsolidatedData.Key1
,ConsolidatedData.Key2
,ConsolidatedData.Key3

Thanks

|||You had already posted those info... from the result you posted I see that you would like to obtain key1, 2 & 3 for each tagid in the criteria table, but is not clear to me why do you used the min(..) function... so I guess I haven't correctly understood what you'd like to accomplish... please explain with your own words which is the goal of the view, not data... words :)