Thread: Comparing Subsets III

Wolffy's comments back in August were very helpful in giving me some direction on how to compare results from two query result sets. However, I'm still fairly perplexed. In the interest of trying to completely replicate the problem and provide good sample data, I'm going to provide some aliased sample data that I hope will make more sense than my previous posts.

I posted on another forum too, but I usually get more polite responses here

Basically, I’m trying to figure out how to exclude any records that have an ID which are associated with any results based on a specific criterion.

In the sample data, you’ll see certain records that have an “opposite” nature in the user interface represented by a “#” sign. After providing the sample data, I will show the desired results:


There are 3 tables:

Code:

CREATE TABLE MASTEREXCEPTIONCOMBINATION
(
EXCCOMBOID INT NOT NULL,
VISIBLE TINYINT NOT NULL
(EXCCOMBOID) PRIMARY KEY)


INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (3,1);
INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (6,0);
INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (7,1);
INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (8,1);
INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (9,0);
INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (11,0);
INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (13,0);
INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (16,0);
INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (18,1);
INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (19,0);
INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (20,0);
INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (21,0);

INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (22,0);
INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (23,0);
INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (24,0);
INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (25,0);
INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (26,1);
INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (27,0);
INSERT INTO MASTEREXCEPTIONCOMBINATION (EXCCOMBOID, VISIBLE)
VALUES (29,0);

Code:

CREATE TABLE EXCEPTIONCOMBINATION
(
EXCCOMBOID INT NOT NULL,
EXCID INT NOT NULL
(EXCCOMBOID) PRIMARY KEY)


INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (3,3)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (3,6)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (6,1)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (7,1)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (7,3)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (7,5)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (8,1)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (8,5)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (8,7)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (9,3)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (9,5)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (11,2)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (13,5)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (16,1)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (16,3)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (18,2)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (18,3)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (18,5)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (19,2)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (19,5)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (20,6)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (20,7)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (21,3)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (22,5)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (22,7)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (23,7)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (24,6)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (25,1)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (25,5)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (26,6)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (26,7)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (26,8)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (27,2)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (27,6)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (29,1)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (29,6)
INSERT INTO EXCEPTIONCOMBINATION (EXCCOMBOID, EXCID)
VALUES (29,7)

Code:

CREATE TABLE EXCEPTION
(
EXCID INT NOT NULL,
EXCABBR VARCHAR(8) NOT NULL
EXCLUSION# INT NOT NULL

(EXCID) PRIMARY KEY)

INSERT INTO EXCEPTION (EXCID, EXCABBR, EXCLUSION#)
VALUES (1,"MWF",0)
INSERT INTO EXCEPTION (EXCID, EXCABBR, EXCLUSION#)
VALUES (2,"T-R",0)
INSERT INTO EXCEPTION (EXCID, EXCABBR, EXCLUSION#)
VALUES (3,"PSD",3)
INSERT INTO EXCEPTION (EXCID, EXCABBR, EXCLUSION#)
VALUES (5,"CSU",5)
INSERT INTO EXCEPTION (EXCID, EXCABBR, EXCLUSION#)
VALUES (6,"#CSU",5)
INSERT INTO EXCEPTION (EXCID, EXCABBR, EXCLUSION#)
VALUES (7,"#PSD",3)
INSERT INTO EXCEPTION (EXCID, EXCABBR, EXCLUSION#)
VALUES (8,"COPO",0)

I need to use the MASTEREXCEPTIONCOMBINATION table to determine which are the visible vs. invisible EXCEPTIONCOMBINATION. This is what I usually write:

Code:

SELECT EXC1.EXCCOMBOID, EXC1.EXCID, EX1.EXCABBR, EX1.EXCLUSION#, EXC2.EXCCOMBOID, EXC2.EXCID, EX2.EXCABBR, EX2.EXCLUSION#
FROM EXCEPTIONCOMBINATION EXC1
JOIN EXCEPTION EX1 ON EX1.EXCID=EXC1.EXCID
LEFT OUTER JOIN EXCEPTIONCOMBINATION EXC2 ON EXC2.EXCID=EXC1.EXCID
LEFT OUTER JOIN EXCEPTION EX2 ON EX2.EXCID=EXC2.EXCID

WHERE
EXC1.EXCCOMBOID IN (SELECT EXCCOMBOID FROM MASTEREXCEPTIONCOMBINATION WHERE VISIBLE=1) AND
EXC2.EXCCOMBOID IN (SELECT EXCCOMBOID FROM MASTEREXCEPTIONCOMBINATION WHERE VISIBLE=0)

The resulting data set is:

3 3 PSD 3 9 3 PSD 3
3 3 PSD 3 16 3 PSD 3
3 6 #CSU 5 20 6 #CSU 5
3 3 PSD 3 21 3 PSD 3
3 6 #CSU 5 24 6 #CSU 5
3 6 #CSU 5 27 6 #CSU 5
3 6 #CSU 5 29 6 #CSU 5
7 1 MWF 0 6 1 MWF 0
7 3 PSD 3 9 3 PSD 3
7 5 CSU 5 9 5 CSU 5
7 5 CSU 5 13 5 CSU 5
7 1 MWF 0 16 1 MWF 0
7 3 PSD 3 16 3 PSD 3
7 5 CSU 5 19 5 CSU 5
7 3 PSD 3 21 3 PSD 3
7 5 CSU 5 22 5 CSU 5
7 1 MWF 0 25 1 MWF 0
7 5 CSU 5 25 5 CSU 5
7 1 MWF 0 29 1 MWF 0
8 1 MWF 0 6 1 MWF 0
8 5 CSU 5 9 5 CSU 5
8 5 CSU 5 13 5 CSU 5
8 1 MWF 0 16 1 MWF 0
8 5 CSU 5 19 5 CSU 5
8 7 #PSD 3 20 7 #PSD 3
8 5 CSU 5 22 5 CSU 5
8 7 #PSD 3 22 7 #PSD 3
8 7 #PSD 3 23 7 #PSD 3
8 1 MWF 0 25 1 MWF 0
8 5 CSU 5 25 5 CSU 5
8 1 MWF 0 29 1 MWF 0
8 7 #PSD 3 29 7 #PSD 3
18 3 PSD 3 9 3 PSD 3
18 5 CSU 5 9 5 CSU 5
18 2 T-R 0 11 2 T-R 0
18 5 CSU 5 13 5 CSU 5
18 3 PSD 3 16 3 PSD 3
18 2 T-R 0 19 2 T-R 0
18 5 CSU 5 19 5 CSU 5
18 3 PSD 3 21 3 PSD 3
18 5 CSU 5 22 5 CSU 5
18 5 CSU 5 25 5 CSU 5
18 2 T-R 0 27 2 T-R 0
26 6 #CSU 5 20 6 #CSU 5
26 7 #PSD 3 20 7 #PSD 3
26 7 #PSD 3 22 7 #PSD 3
26 7 #PSD 3 23 7 #PSD 3
26 6 #CSU 5 24 6 #CSU 5
26 6 #CSU 5 27 6 #CSU 5
26 6 #CSU 5 29 6 #CSU 5
26 7 #PSD 3 29 7 #PSD 3

As I said earlier, the # sign in the Excabbr column indicates that it is opposite to the Excabbr mirror image without the #. So for example #CSU and CSU are opposites. #PSD and PSD are opposites and can not exist in the same subset together.

So if you look at EXC1.EXCCOMBOID where EXC1.EXCCOMBOID=3, it returns the following:

3 3 PSD 3 9 3 PSD 3
3 3 PSD 3 16 3 PSD 3
3 6 #CSU 5 20 6 #CSU 5
3 3 PSD 3 21 3 PSD 3
3 6 #CSU 5 24 6 #CSU 5
3 6 #CSU 5 27 6 #CSU 5
3 6 #CSU 5 29 6 #CSU 5

The problem is that if you look at EXC2.EXCCOMBOID where EXC2.EXCOMBOID=9, it contains an EX2.EXCABBR that equals ‘CSU’. Because it has been marked with that EXCABBR at least once, it needs to be excluded from its association with EXC1.EXCCOMBOID=3. Likewise, I don’t want EXC2.EXCCOMBOID in (20, 29) since they are associated with ‘#PSD’ and/or ‘CSU’.

So my desired results if I only concentrated on EXC1.EXCCOMBOID=3 would be:

3 3 PSD 3 16 3 PSD 3
3 3 PSD 3 21 3 PSD 3
3 6 #CSU 5 24 6 #CSU 5
3 6 #CSU 5 27 6 #CSU 5


I know this is a bit convoluted, I hope it’s making sense! Any help will be greatly appreciated!

OK, sorry, don't get it. I can't figure out from your post why the records in red are to be excluded from your final results set.

Can you elaborate on the last paragraph some more. I'm sure it's back in you last thread, but how is ID 9 known to be related to ID 3?

Usually, when there is difficulty in writing a query to solve a problem, it's because the database design is poor or inflexible. I doubt you have ability to change the design any (that would break existing applications, no doubt) but consider writing VIEWs to refactor the data into a more flexible design. For example, prepending a hash to the school ID is rather a poor design -- rather it should be a bit field for inSession/outOfSession. This can be done in a VIEW:

Code:

Select
  Case 
    When left(schoolID,1) = '#' then right(schoolID, 3) -- assumes all IDs are 3 chars
    Else schollID
  End as SchoolID
, Case
    When left(schoolID, 1) = '#' then 1
    Else 0
  End as outOfSession

I'm guess that rethinking the problem into what table design WOULD work, and then doing some VIEWs (Stored Queries in Access) that support the design would make this problem easier.

I've been busy doing work that I get paid to do, so haven't been able to expend too many brain cells here. However, I think if you/we concentrate on the problem in the last paragraph first, we should be able to sort this. (i.e. chunk it down into smaller bits).

Nevertheless, I do need to find a way to work around it. I can use CTE's but the problem with views is the front end users won't be able to access them. I can do those things directly in SQL Server, but can not distribute them to the front end users.

OK, I think I'm beginning to understand the whole problem. However, I'm confused about your second paragraph. I think I take this to mean that this is a third-party application and that your front-end users are using this third-party application's user interface and you can't/don't-want-to change that. If that is the case, that is quite understandable. So then, you are trying to (a) reverse engineer the third-party database design (or maybe you even have the third-part schema to work with) and (b) develop a report (and I'll use report of any kind of output to be used by a human) for supervisors/administrators rather than the front-end users.

If that is this case, then consider this out-of-box idea. Since you mention CTE's, that means you have SQL Server 2005/2008. You could create another database on the same server and use Views to mung up the data (as I mentioned before) by looking back at the original, third-party database. For example (and a really simple one at that):

Code:

Use MySecretDatabase
Go
Create View MyView As
Select *
  From ThirdPartyDatabase.dbo.ExceptionCombination

There are some advantages to this; (a) the third-party may (heck probably will) change the design of the database in the future -- and thus break your reporting application and (b) you don't need to add non-third-party code to the third-party database and (c) when it does break, all you need do is change your database views and your reporting application will continue to work.

Now as to the actual problem at hand -- I don't have a clue yet. At least I know it's not your design. I'll think on it in my spare cycles. One question tho -- does your reporting application need to work with 'real-time' data, or is 'near-time' need acceptable (such as data from yesterday). If the later, then perhaps consider using some stored procedures to mung up the data to make your reporting easier. You can (a)do alot more in a stored procedure than you can in a query and (b)it doesn't matter if it runs for minutes, or even hours, to get the job done.

Note that I merged the two threads together on this subject so that all the information can be found in one place.

So if you look at EXC1.EXCCOMBOID where EXC1.EXCCOMBOID=3, it returns the following:

3 3 PSD 3 9 3 PSD 3
3 3 PSD 3 16 3 PSD 3
3 6 #CSU 5 20 6 #CSU 5
3 3 PSD 3 21 3 PSD 3
3 6 #CSU 5 24 6 #CSU 5
3 6 #CSU 5 27 6 #CSU 5
3 6 #CSU 5 29 6 #CSU 5

The problem is that if you look at EXC2.EXCCOMBOID where EXC2.EXCOMBOID=9, it contains an EX2.EXCABBR that equals ‘CSU’. Because it has been marked with that EXCABBR at least once, it needs to be excluded from its association with EXC1.EXCCOMBOID=3. Likewise, I don’t want EXC2.EXCCOMBOID in (20, 29) since they are associated with ‘#PSD’ and/or ‘CSU’.

This is the bit that I still don't see. Specifically the bit that I bolded. Perhaps I don't understand what 'it' refers to. I see the following combinations in the ExceptionCombinationTable:

Code:

(3,3)
(3,6)
(9,3)
(9,5)

Now, I take it to mean that the first number identifies the Exception Record, thus we really have 2 exceptions here. The second number is really a Foreign Key into the Exception table, and thus

Code:

3 = PSD
5 = CSU
6 = #CSU

Now, are you saying that the (9,5) record should be excluded because of the (3,6) record in that CSU and #CSU and mutually exclusive? (if so, which record is considered the 'good' record) And the (9,3) record should be excluded because of the (3,3) record in that both records indicate an ExcAbbr of PSD? (i.e. ID=3)? (again, which is the 'good' record?)