Mutually Exlusive Results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Mutually Exlusive Results

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2010-05-21 : 04:42:03

I've been trying to deal with the following scenario for some time. I've found workarounds and compromises but the solutions can be fairly clumsy, so I'd like to see if I can broach the subject again.

In a nutshell, I would like to be able to compare all the results of one query to those of another in order to identify mutually exclusive conditions.

Frequently, the advise I've gotten or what I see online is this:

Select *
From Table A
Full Join Table B on a.col1=b.col1
where (a.col1 is null or b.col1 is null)

Or something to that effect. The problem with this is that it only handles single rows. More on this later.

Here is a set of data that I'm working with:

create table #Exception (comboid int, excid int, exclusion int, excabbr char(4), visible int)
insert into #Exception (comboid, excid, exclusion, excabbr, visible)
values (1, 100, 100, 'CSU',1);
insert into #Exception (comboid, excid, exclusion, excabbr, visible)
values (1, 108, 108, 'PSD',1);
insert into #Exception (comboid, excid,exclusion, excabbr, visible)
values (1, 110, 110, 'MWF',1);
insert into #Exception (comboid, excid,exclusion, excabbr, visible)
values (2, 100, 100, 'CSU',0);
insert into #Exception (comboid, excid,exclusion, excabbr, visible)
values (2, 109, 108, '#PSD',0);
insert into #Exception (comboid, excid,exclusion, excabbr, visible)
values (3, 100, 100, 'CSU',0);
insert into #Exception (comboid, excid,exclusion, excabbr, visible)
values (3, 108, 108, 'PSD',0);

select * from (
select *
from #Exception
where visible=1) a
cross join
(select *
from #Exception B
where visible=0) b

drop table #exception

The reason I am cross joining the table against itself is to display the temp table essentially as 2 tables. This is the condition I'm working with in that database. There is 1 table, however, some results are "visible" some are "invisible". The records where visible=0 is, for all intents and purposes, a sub record of the records where visible=1.

With that said, in the above dataset, my desired output would be:

1 110 110 MWF 1 3 100 100 CSU 0
1 110 110 MWF 1 3 108 108 PSD 0
1 108 108 PSD 1 3 100 100 CSU 0
1 108 108 PSD 1 3 108 108 PSD 0
1 100 100 CSU 1 3 100 100 CSU 0
1 100 100 CSU 1 3 108 108 PSD 0

The reason for this is that any time the exlusion number for two comboid's is the same, but the excid is different, the two records can't exist together. However, that also means that for the comboid's that are being compared, if that exclusion exists in any row for the two comboid's being compared, they must be excluded.

So for example, the following results must be excluded:

1 100 100 CSU 1 2 100 100 CSU 0
1 100 100 CSU 1 2 109 108 #PSD 0
1 108 108 PSD 1 2 100 100 CSU 0
1 108 108 PSD 1 2 109 108 #PSD 0
1 110 110 MWF 1 2 100 100 CSU 0
1 110 110 MWF 1 2 109 108 #PSD 0

This is because comboid 1 has an exclusion 108 (or excabbr PSD) and comboid 2 has the same exclusion 108, however, notice the excid are different. For comboid 1, it is 108, for comboid 2 it is 109. I need to be able to say, "comboid 1 does not exist with comboid 2" because at least one of the subcomponents of comboid 1 and comboid 2 are mutually exclusive, therefore, the comboid's are in fact mutually exclusive.

Any ideas?

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2010-05-22 : 16:20:05

Ok...I think this is what I want. Not sure if I'm making some false assumptions, but in any case, with the small set of sample data I created, this is working:

create table #Exception (comboid int, excid int, exclusion int, excabbr char(4), visible int)
insert into #Exception (comboid, excid, exclusion, excabbr, visible)
values (1, 100, 100, 'CSU',1);
insert into #Exception (comboid, excid, exclusion, excabbr, visible)
values (1, 108, 108, 'PSD',1);
insert into #Exception (comboid, excid,exclusion, excabbr, visible)
values (1, 110, 110, 'MWF',1);
insert into #Exception (comboid, excid,exclusion, excabbr, visible)
values (2, 100, 100, 'CSU',0);
insert into #Exception (comboid, excid,exclusion, excabbr, visible)
values (2, 109, 108, '#PSD',0);
insert into #Exception (comboid, excid,exclusion, excabbr, visible)
values (3, 100, 100, 'CSU',0);
insert into #Exception (comboid, excid,exclusion, excabbr, visible)
values (3, 108, 108, 'PSD',0);

select * from
(select a.comboid as a_comb, b.comboid as b_comb from
(select *
from #Exception
where visible=1) a
cross join
(select b.comboid
from #Exception B
where visible=0) b) x
left outer join
(select a.comboid as a_comb, b.comboid as b_comb from
(select *
from #Exception
where visible=1) a
cross join
(select b.comboid, B.excid, b.exclusion
from #Exception B
where visible=0) b
where a.excid<>b.excid and a.exclusion=b.exclusion) y on x.a_comb=y.a_comb and x.b_comb=y.b_comb
where y.a_comb is null and y.b_comb is null

drop table #exception

It only shows results of records where the subsets of the comboid's don't present a mutually exclusive situation.

In any case, let me know if anyone thinks there are problems with this.

Thanks!

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-05-22 : 16:34:00

Not sure if this will work for you, but lookup EXCEPT in books online. You can use that to compare results from two or more queries and return the rows that don't match.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2010-05-22 : 18:09:11

Hey Jeff,

I thought off using Except...the problem is that only compares one row at a time. I needed to compare the result set of one combination to the result set of another combination which has always been an issue.

Here's the basic idea:

Combination 1 has the following attributes: (CSU, PSD, MWF)
The # character means "not" so for example, #PSD means "not PSD' So two combinations can not exist together if one of the combinations has a mutually exclusive attribute to the results of another combination.

This is why ComboId 1 and ComboId 2 are mutually exclusive, because one of the rows of 1 has PSD and one of the rows of 2 has #PSD.

If a simple Except query could handle that, it would be great. But the results of an except query would still show me Comboid 1 and 2 together because they both have attributes that can coexist together. Basically, I'm looking for the lowest common denominator, if they are mutually exclusive, the whole combination has to be excluded.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources