I have three tables with the following format
A B C ----- ----- ----- aKey bKey fk_c_b fk_b_a c_enum c_value
bKey are the primary key for each table,
C does not have a primary key.
C.fk_c_b are the foreign keys that points to the parent table.
c_enum is a enum of 87 different values.
C.c_value is float that can be any value
The relationships between the tables are:
A.aKey) 0..1 --- * (
B.bKey) 0..1 --- * (
- 57,668 rows in A
- 650,768 rows in B
- 34,734 rows in C
I need to display a distinct list of all
c_enum values and their parents, however If two rows in
C share the same
c_value values but different
c_enum values I can use not those two rows in the distinct list.
select aKey, bKey, c_enum, c_value, newid() as id into #myResultSet from A inner join B on fk_b_a = aKey inner join C as temp1 on fk_c_b = bKey where c_value not in (select c_value from C as temp2 where temp1.c_enum <> temp2.c_enum and temp1.fk_c_b = temp2.fk_c_b and temp1.c_value = temp2.c_value) --This is the step that I do not know how to optimize delete #myResultSet where id <> (select top 1 id from #myResultSet as t1 where #myResultSet.c_enum = t1.c_enum)
Also in that process I need to minimize the the distinct count of aKey and bKey. So If I did
select count(distinct aKey), count(distinct bKey), count(*) from #myResultSet
the result would have the minimum values possible. The optimal result is
1, 1, 87 and the worst case would be
87, 87, 87. My current method does not attempt to get minimal values (my results are
14, 17, 87 which is not bad, but I think that is just luck with this dataset, running on a different dataset may return worse results) and I am having a lot of trouble figuring out how to do it the right way.
What would be the correct way to solve this problem? My current method is not optimal all and I have been having a lot of trouble figuring out what I need to do to build
#myResultSet with minimal values for
count(distinct aKey) and