OK, bear with me here, this is a big query. In thinking on this, I believe the key is the Mutually Exclusive Pairs table -- and we need to setup a Primary Key field on this table. Now, I think we can look at the Visible and Not Visible data as separate tables, logically or physically it doesn't really matter. Let's call these V and N
Now, consider the following sample data:
Code:
Table: V
ID--ExcID
10 1
10 2
Table: N
ID--ExcID
13 3
13 4
14 2
14 3
15 3
15 6
17 6
Now, let's assume the following exclusive pair: (1,5) and (2, 4). These would go into a table thus:
Code:
Table: M
ID--ExcID1--ExcID2
21 2 4
22 1 5
Now, we can 'pad out' the V and N table to include the ID value from the M table by doing an outer join -- the resulting sets being:
Code:
Table: V
ID--ExcID--MExID
10 1 22
10 2 21
Table: N
ID--ExcID--MExID
13 3 Null
13 4 21
14 2 21
14 3 Null
15 3 Null
15 6 Null
17 6 Null
Now the fun beings. We can build the following CTE. First there are a number of records in the N table that we can consider 'SAFE' in that they don't contain any ExcID that have a MutEx pair -- in this case 15 and 17.
Code:
With SAFE_N as (
Select ID, ExcID, MExID
From N
Where ID Not IN (Select Distinct ID From N Where MexID is not null)
this gives us the records (15, 3), (15, 6) and (17, 6).
Next we need to get the other records from N, and let's call this UNSAFE_N
Code:
With UNSAFE_N as (
Select ID, ExcID, MExID
From N
Where ID Not IN (Select Distinct ID From SAFE_N)
)
This gives us (13, 3), (13, 4), (14, 2) and (14, 3)
Now in those UNSAFE records, some should be excluded (13 in this case) while some really are OK (14). Here 14 is OK because (10, 2) and (14, 2) are compatible since they have the SAME ExcID. So, let's get the ID's of the UNSAFE records that are really OK to be included:
Code:
With OK_N_ID as (
select distinct UNSAFE_N.ID from V
JOIN UNSAFE_N
On (UNSAFE_N.MexID = V.MExID And V.ExcID = UNSAFE_N.ExcID)
)
This returns 14.
Using the results of the previous CTE, we can get the records from UNSAFE that are indeed OK to include:
Code:
With OK_N as (
Select * from N
Where ID In (Select ID from OK_N_ID)
)
Finally, we can put it all together to get our final results:
Code:
Select * from V
UNION
Select * from OK_N
UNION
Select * from SAFE_N
Which with the sample data yields:
Code:
10 1 22
10 2 21
14 2 21
14 3 NULL
15 3 NULL
15 6 NULL
17 6 NULL
Now, this doesn't address the problem of MutEx pairs within the V table, but should be most of the work. To scrub the V table, it would probably be similar to the above.
I'm sure this isn't the perfect solution, but it is another way to come at the problem. Yes, the query is ugly, but if there isn't too much data, it shouldn't run too long.
Putting the above together into a query:
Code:
With SAFE_N as (
Select ID, ExcID, MExID
From N
Where ID Not IN (Select Distinct ID From N Where MexID is not null)
)
, UNSAFE_N as (
Select ID, ExcID, MExID
From N
Where ID Not IN (Select Distinct ID From SAFE_N)
)
, OK_N_ID as (
select distinct UNSAFE_N.ID from V
JOIN UNSAFE_N
On (UNSAFE_N.MexID = V.MExID And V.ExcID = UNSAFE_N.ExcID)
)
, OK_N as (
Select * from N
Where ID In (Select ID from OK_N_ID)
)
Select * from V
UNION
Select * from OK_N
UNION
Select * from SAFE_N
LinkBack URL
About LinkBacks
Reply With Quote
If you follow Southpark at all and go back to the Harbuck's episode where Tweak's father describes his coffee something like this, "It's warm, rich blend with notes of pear and orchids" However, in reality, it tastes like mud, so you might want to stick with your Harbuck's 
Bookmarks