Mutually Exlusive Sets

[SCFlam]

Hi, I'm new to this forum, so forgive me if this is too much information or not the right forum.

I've been struggling with a strategy for getting this information for somet time and I'd like to see if there's a way to effectively exclude records based on the result set of a query.

Sample Data:


Create Table Exceptions (excid int, abbr varchar(5), numexclusion int);
insert into Exceptions (excid, abbr, numexclusion) values (10, 'CSU', 1);
insert into Exceptions (excid, abbr, numexclusion) values (11, '#CSU', 1);
insert into Exceptions (excid, abbr, numexclusion) values (12, 'PSD', 2);
insert into Exceptions (excid, abbr, numexclusion) values (13, '#PSD', 2);

Create Table Combinations (exccomboid int, excid int);
insert into Combinations (exccomboid, excid) values (1, 10);
insert into Combinations (exccomboid, excid) values (1, 12);
insert into Combinations (exccomboid, excid) values (2, 10);
insert into Combinations (exccomboid, excid) values (2, 13);
insert into Combinations (exccomboid, excid) values (3, 11);
insert into Combinations (exccomboid, excid) values (3, 12);
insert into Combinations (exccomboid, excid) values (4, 11);
insert into Combinations (exccomboid, excid) values (4, 13);


Select ex.excid, ex.abbr, ex.numexclusion, exc.exccomboid
From Combinations exc
Join Exceptions Ex on Ex.excid=Exc.excid

Results:

exccomboid abbr numexclusion excid
1 CSU 1 10
1 PSD 2 12
2 CSU 1 10
2 #PSD 2 13
3 #CSU 1 11
3 PSD 2 12
4 #CSU 1 11
4 #PSD 2 13

The application that I use calls the data from these tables. Within the application, the behaviour of the Combinations.Exccomboid is that it acts as a "single" record with a subset of data. So, for example, given the results of the select statement above, the exccomboid column gives 2 results for each distinct exccomboid. In essence, there are 4 combinations, each with one duple. So, the application actually views the data in this way:

If ExcComboid=1 then excid in (10,12)
If ExcComboid=2 then excid in (10,13)
If ExcComboid=3 then excid in (11,12)
If ExcComboid=4 then excid in (11,13)

Here's the complication, I have another table:

Create Table MasterC (exccomboid int, ComboAbbr varchar, Visible tinyint);
insert into MasterC (exccomboid, comboabbr, visible) values (1, 'CP', 1);
insert into MasterC (exccomboid, comboabbr, visible) values (8, null, 0);
insert into MasterC (exccomboid, comboabbr, visible) values (2, 'CP#', 1);
insert into MasterC (exccomboid, comboabbr, visible) values (2, null, 0);
insert into MasterC (exccomboid, comboabbr, visible) values (3, 'C#P', 1);
insert into MasterC (exccomboid, comboabbr, visible) values (3, null, 0);
insert into MasterC (exccomboid, comboabbr, visible) values (4, 'C#P#', 1);
insert into MasterC (exccomboid, comboabbr, visible) values (4, null, 0);

The "visible" column controls whether or not end users can see the combination in the application. If "visible" =0 then the user doesn't see it, it is a control for the application itself. Essentially, I need to use the MasterC table as a divider between the user oriented combos and the application oriented combos. When I join the table to itself, the resulting exccomboid's that are mutually exclusive must be filtered out.

Take the following query:

Select MC.ComboAbbr, MC.ExcComboid, V.Abbr, V.NumExclusion, V.Excid, N.Abbr, N.NumExclusion, N.Excid
From MasterC
Left Join
(
Select ex.excid, ex.abbr, ex.numexclusion, exc.exccomboid
From Combinations exc
Join Exceptions Ex on Ex.excid=Exc.excid
Where exc.exccomboid in (select exccomboid from MasterC where visible=1)) V on V.ExcComboid=MC.ExcComboid
Left Join
(
Select ex.excid, ex.abbr, ex.numexclusion, exc.exccomboid
From Combinations exc
Join Exceptions Ex on Ex.excid=Exc.excid
Where exc.exccomboid in (select exccomboid from MasterC where visible=N)) N on N.ExcComboid=MC.ExcComboid

In this query, I want to see the results from V (or visible sets) that are able to coincide with the results from N (or sets that are not visible).

Going back to the example of:

A: If ExcComboid=1 then excid in (10,12)
B: If ExcComboid=2 then excid in (10,13)
C: If ExcComboid=3 then excid in (11,12)
D: If ExcComboid=4 then excid in (11,13)

the goal would be to say that a combination that came from the results of V could not coincide with he results of N if one of the elements of a set from either V or N was mutually exclusive from eachother. The NumExclusion field is the key there. So in the above example,

A: If ExcComboid=1 then excid in (10,12) is mutually exclusive from
B: If ExcComboid=2 then excid in (10,13)

This is because PSD (excid=12) and #PSD (excid=13) are opposites of eachother and therefore precludes the 2 combinations from working together. I need to figure out a way to say the ExcComboid=1 can not coincide with ExcComboid=2 (and that ExcComboid=3 can not coincide with ExcComboid=4); as noted, the reasoning is that two sets (an exccomboid is equivalent to 1 set) can not coexist if one of its elements or components is mutually exclusive from an element or component of another set.

I know this is long and may be confusing, but I've tried about 10 times to explain it and I am not sure how to make it comprehensible.

Thanks!

 

[SCFlam]

Quick edit, last part of last query, visible=0 not visible=N,
here:

Select ex.excid, ex.abbr, ex.numexclusion, exc.exccomboid
From Combinations exc
Join Exceptions Ex on Ex.excid=Exc.excid
Where exc.exccomboid in (select exccomboid from MasterC where visible=N)) N on N.ExcComboid=MC.ExcComboid

should be:

Select ex.excid, ex.abbr, ex.numexclusion, exc.exccomboid
From Combinations exc
Join Exceptions Ex on Ex.excid=Exc.excid
Where exc.exccomboid in (select exccomboid from MasterC where visible=0)) N on N.ExcComboid=MC.ExcComboid

 

[RyanEK]

Umm yeah a little confusing. Why don't you try providing sample data and then showing the desired output?