I am trying to write a query to exclude certain records from a query. Normally, of course, I'd do it with a NOT IN or use SUBTRACT. However, I can't do this with these records because it doesn't work correctly. There is a table called SHRTRCE that has fields for PIDM, SUBJ, COURSE and TITLE, coded as follows:
create table SHRTRCE
(
PIDM NUMBER NOT NULL
SUBJ VARCHAR2(10)
COURSE VARCHAR2(6)
TITLE VARCHAR2(30)
)
/
INSERT INTO SHRTRCE
(PIDM,SUBJ,COURSE,TITLE)
VALUES
(12345,MATH,1X0501,COLLEGE ALGEBRA)
/
INSERT INTO SHRTRCE
(PIDM,SUBJ,COURSE,TITLE)
VALUES
(12345,ENG,2X1001,COMPOSITION II)
/
INSERT INTO SHRTRCE
(PIDM,SUBJ,COURSE,TITLE)
VALUES
(12345,PSYC,1X0101,GENERAL PSYCH)
/
INSERT INTO SHRTRCE
(PIDM,SUBJ,COURSE,TITLE)
VALUES
(23456,ENG,1X1001,COMPOSITION I)
/
INSERT INTO SHRTRCE
(PIDM,SUBJ,COURSE,TITLE)
VALUES
(23456,PHY,1X3201,NON-LAB PHYSICS)
/
INSERT INTO SHRTRCE
(PIDM,SUBJ,COURSE,TITLE)
VALUES
(34567,MATH,1X0501,COLLEGE ALGEBRA)
/
INSERT INTO SHRTRCE
(PIDM,SUBJ,COURSE,TITLE)
VALUES
(34567,MATH,2X2010,CALCULUS I)
/
What I want to do is pull ONLY the records for those PIDMS where there are no courses with the SUBJ of MATH. So, in this case, I only want the values for PIDM 23456. If I use NOT IN, I still get PIDMs in my list where the person actually has one or more MATH courses; it only excludes those courses that are MATH, but leaves the others. If I use a SUBTRACT, the same results occur. I'm not sure how to proceed at this point. I'm going to continue to hack at it, but thought I'd post something because I know this is probably something fairly easy, I'm just not finding it in my books anywhere.
Thanks so much,
Michelle Craig
Data Coordinator
Kent State University
Edited by: BluShadow on 10-Jan-2012 14:23
fixed {noformat}
{noformat} tags. the word "code" must be lower case in both tags