Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2010
    Posts
    1

    Unanswered: Mutually Exclusive Sets

    Hey folks,

    I'm new to this forum and 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. I'm hoping to be able to set up a query that will be dynamic in that it will be able to "read" new combinations that are created based on rules set up in the query which will automatically recognize sets that can exist together and filter out sets that should be mutually exclusive.

    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 smallint);
    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=0)) 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!

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I know this is long and may be confusing, ...
    Yes, it definitely is. Without your DDL and DMLs I wouldn't have given it a second read. Luckily for you, I don't have a life

    I got lost in your explanation and I got a few error messages when executing the DDL's and DML's, also the results you gave are different (column order) from the results returned from the SELECTs, it's not that important, but it adds to the confusion. Can you recheck your DDL's and DML's and verify they are correct? It's pretty complex, so try to make this as easy as it can be for others to understand.

    Code:
    insert into MasterC (exccomboid, comboabbr, visible) values (8, null, 0);
    Is that a typo? should it be
    Code:
    insert into MasterC (exccomboid, comboabbr, visible) values (1, null, 0);
    so it's in line with the other data?
    If that is true (the 8 should have been a 1), both V and N are the same and then it makes no sense to me any more.
    If the 8 was correct, please recheck the sample INSERT scripts, as 8 was never defined in the example data.

    ...The NumExclusion field is the key there. ...
    You don't specify in what way. Did you mean that the combination of ExcComboid and NumExclusion gives a unique key if one would join Combinations with Exceptions?

    Can you give a result that you want to have? You say that ExcComboid=1 is mutually exclusive from ExcComboid=2. Are there any other ExcComboid's with which it is compatible with? It seems to me ExcComboid=1 is mutually exclusive from any other ExcComboid.

    ATM it's unclear to me what you want and what you don't want. Perhaps with some good example data of what results you want, things will become more clear.

    Edit: corrected typo
    Last edited by Wim; 07-26-10 at 05:57.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •