SQL Query using NOT IN - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Query using NOT IN

Author

Topic

anishap
Yak Posting Veteran

61 Posts

Posted - 2008-01-15 : 14:43:45

I have a table with fields FLDTYPE and FLDTYPE2. I want to list all rows from this table excluding the codes 'BBEFU', 'BPEF3M', 'BPEF6M' from both the fields.

The below code is not working.

WHERE ( T.FLDTYPE NOT IN ( 'BBEFU', 'BPEF3M', 'BPEF6M' ) OR G.FLDTYPE2 NOT IN ( 'BBEFU', 'BPEF3M', 'BPEF6M' ) )

Please let me know.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-15 : 14:55:37

WHERE T.FLDTYPE NOT IN ( 'BBEFU', 'BPEF3M', 'BPEF6M' ) AND G.FLDTYPE2 NOT IN ( 'BBEFU', 'BPEF3M', 'BPEF6M' )

"...database development, while a serious pursuit and vitally important to business, should be fun!"
-Adam Machanic

anishap
Yak Posting Veteran

61 Posts

Posted - 2008-01-15 : 15:05:22

I tried using 'AND' but then it should satisfy both requirement. This won't work for me.
Anyway thanks for your response

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-15 : 15:14:24

quote:
Originally posted by anishap

I tried using 'AND' but then it should satisfy both requirement. This won't work for me.
Anyway thanks for your response

Maybe im not understanding what you intend your output to be. Can you give an example of your expected output?

"...database development, while a serious pursuit and vitally important to business, should be fun!"
-Adam Machanic

raaj
Posting Yak Master

129 Posts

Posted - 2008-01-15 : 15:26:21

if u could provide some sample data...like wht r the rows in the fldtype and fldtype2...then tht wud be helpful in answering ur question.........

anishap
Yak Posting Veteran

61 Posts

Posted - 2008-01-15 : 15:33:48

SELECT C.FLDINJDATE, C.FLDINJTIME, C.FLDITEMINVL, C.FLDDEPT, C.FLDLOCATION, C.FLDHOWOCCUR,
C.FLDCASENUM, C.FLDREC_NUM, C.FLDACTIVITY, C.FLDWHEROCCR,
E.FLDLNAME, E.FLDSSN, E.FLDID, E.FLDMI, E.FLDFNAME, G.FLDTYPE,G.FLDTYPE2 T.FLDCODE,
W.FLDSHRPBRND, W.FLDSHRPMODL
FROM LOG C, EMPLOYEE E, WCOMP W, ELOG G, LOGTYPE T
WHERE C.FLDEMPLOYEE = E.FLDREC_NUM AND
G.FLDCASE = C.FLDREC_NUM AND
C.FLDREC_NUM *= W.FLDCASE (+) AND
G.FLDTYPE = T.FLDCODE AND
( T.FLDCODE NOT IN ( 'BBEFU', 'BPEF3M', 'BPEF6M' ) OR G.FLDTYPE2 NOT IN ( 'BBEFU', 'BPEF3M', 'BPEF6M' ) ) AND
C.FLDINJDATE >= '2007-01-01' AND
C.FLDINJDATE <= '2008-01-15' AND
G.FLDCLINIC = 'HMC'

This is the actual code I tried.

anishap
Yak Posting Veteran

61 Posts

Posted - 2008-01-15 : 15:35:33

Sample data joining EMPLOYEE and ELOG TABLE.

EID FLDTYPE FLDTYPE2 DATE
860008096 RESPCL 120 2006-03-19 00:00:00.000
860008096 BPE NULL 2007-07-15 00:00:00.000
860008096 113 BBEFU 2007-07-27 00:00:00.000
860008096 BPEF3M 108 2007-10-11 00:00:00.000

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-15 : 16:23:58

quote:
Originally posted by anishap

Sample data joining EMPLOYEE and ELOG TABLE.

EID FLDTYPE FLDTYPE2 DATE
860008096 RESPCL 120 2006-03-19 00:00:00.000
860008096 BPE NULL 2007-07-15 00:00:00.000
860008096 113 BBEFU 2007-07-27 00:00:00.000
860008096 BPEF3M 108 2007-10-11 00:00:00.000

Using the above sample data what would your desired output be?

"...database development, while a serious pursuit and vitally important to business, should be fun!"
-Adam Machanic

raaj
Posting Yak Master

129 Posts

Posted - 2008-01-15 : 16:27:57

Hi,
I am new to sql server....I am not sure whether the below one is correct or not...just giving a try...but i think this is something which ur looking for...i just create some sample table and tried...let me know is this the one which ur looking for...

create table abc (fldtype varchar(20),fldtype2 varchar(20))

insert into abc values ('FGAT3G','JSHDG3')
insert into abc values ('QJATD22','LOPIU2')
insert into abc values ('BBEFU','BPEF3M')
insert into abc values ('FGAT3G','JSHDG3')
insert into abc values ('BPEF6M','JSHDG3')
insert into abc values ('BHSRDE','KI3DSZ')
insert into abc values ('KUT56','BPEF3M')

select * from abc

select case fldtype when 'BBEFU' then ''
when 'BPEF3M' then ''
when 'BPEF6M' then ''
else fldtype end as FLDTYPE,
case fldtype2 when 'BBEFU' then ''
when 'BPEF3M' then ''
when 'BPEF6M' then ''
else fldtype2 end as FLDTYPE1 from abc

anishap
Yak Posting Veteran

61 Posts

Posted - 2008-01-15 : 18:57:07

In the below query I have joined table Log and Elog. The reason is I need to pull info based on ELOG.CLINIC. But I don't need all the info that is added through ELOG. I need to exclude the following codes 'BBEFU', 'BPEF3M', 'BPEF6M'. Let me know if this helps to understand my question.

SELECT C.FLDINJDATE, C.FLDINJTIME, C.FLDITEMINVL, C.FLDDEPT, C.FLDLOCATION, C.FLDHOWOCCUR,
C.FLDCASENUM, C.FLDREC_NUM, C.FLDACTIVITY, C.FLDWHEROCCR,
E.FLDLNAME, E.FLDSSN, E.FLDID, E.FLDMI, E.FLDFNAME, G.FLDTYPE,G.FLDTYPE2 T.FLDCODE,
W.FLDSHRPBRND, W.FLDSHRPMODL
FROM LOG C, EMPLOYEE E, WCOMP W, ELOG G, LOGTYPE T
WHERE C.FLDEMPLOYEE = E.FLDREC_NUM AND
G.FLDCASE = C.FLDREC_NUM AND
C.FLDREC_NUM *= W.FLDCASE (+) AND
G.FLDTYPE = T.FLDCODE AND
( T.FLDCODE NOT IN ( 'BBEFU', 'BPEF3M', 'BPEF6M' ) OR G.FLDTYPE2 NOT IN ( 'BBEFU', 'BPEF3M', 'BPEF6M' ) ) AND
C.FLDINJDATE >= '2007-01-01' AND
C.FLDINJDATE <= '2008-01-15' AND
G.FLDCLINIC = 'HMC'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-15 : 19:40:13

Did you try Jdaman's suggestion about using AND? We are having a hard time figuring out what you want because we do not have sample data to run a query against. If you can provide your DDL (Table structure), scripts to load those table with sample data and expected output we can help you with your query.

I jsut took a shot in the dark and used Jdaman's suggestion by using an AND and I get the data I expect, but maybe you cna help us with your requirements? Here is a sample that I made up to try the difference between using OR and AND:

DECLARE @Foo TABLE (ID INT IDENTITY(1,1), Val INT)
DECLARE @Bar TABLE (ID INT IDENTITY(1,1), Val INT)

INSERT @Foo
SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7


INSERT @Bar
SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10

SELECT *
FROM @Foo AS F, @Bar AS B
WHERE 
	F.Val NOT IN (6, 7)
	AND B.Val NOT IN (6,7)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources