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
 Comparing Data Sets

Author  Topic 

flamblaster
Constraint Violating Yak Guru

384 Posts
Posted - 2009-10-31 : 19:24:01
I’ve tried several times to articulate this problem and as such, have not been able to resolve the issue since my explanations usually fall short.

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:

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);

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)


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:

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!

flamblaster
Constraint Violating Yak Guru

384 Posts
Posted - 2009-11-08 : 13:53:36
Anyone have any ideas on this?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-11-09 : 11:23:51
sorry your sample data doesnt make much sense . can you explain how you arrived at excluding the red ones?
Go to Top of Page

flamblaster
Constraint Violating Yak Guru

384 Posts
Posted - 2009-11-09 : 18:15:33
Hi Vis,
Yes, I realize I'm having a hard time explaining it. I need to exclude any relationship between
EXC1.EXCCOMBOID IN (SELECT EXCCOMBOID FROM MASTEREXCEPTIONCOMBINATION WHERE VISIBLE=1) AND
EXC2.EXCCOMBOID IN (SELECT EXCCOMBOID FROM MASTEREXCEPTIONCOMBINATION WHERE VISIBLE=0)
where there is a foreign key (EXC2.EXCID) that is mutually exclusive to the foreign key (EXC1.EXCID).

The mutually exclusive records have a # symbol next to them. Also, the mutually exclusive records always have the same Exclusion#.
So if EXC1.EXCCOMBOID=3 and has the foreign keys of EXC1.EXCID=3 and EXC1.EXCID=6, (abbreviations are: 'PSD' and '#CSU'), then any invisible record that has one or more mutually exclusive foreign keys associated with it (ex: 'PSD' and 'CSU) must be excluded.

Is that making more sense?
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site