Simple Query Question - 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

SQL Server Development (2000)

Simple Query Question

Author

Topic

misterzr
Starting Member

49 Posts

Posted - 2006-09-21 : 13:11:49

I have a table (proddta.f4105)that I need to find all the items (COLITM) that only have a 06 and 08 and no 01,02,07 in the COLEDG column

Thanks in advance

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-09-21 : 13:14:52

SELET COLITM
FROM proddts.f4105
WHERE COLEDG IN ('06', '08')

misterzr
Starting Member

49 Posts

Posted - 2006-09-21 : 13:16:18

It has to have 06,and 08's but no matching numbers with 01,02,07

Gopi Nath Muluka
Starting Member

22 Posts

Posted - 2006-09-21 : 13:18:07

try this
select * from proddta.f4105
where COLITM in ('06','08' ) and COLEDG not in ('01','02','07')

Thanks,
Gopi Nath Muluka

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-09-21 : 13:18:22

Sorry, can you provide some sample data from COLEDG?

Gopi Nath Muluka
Starting Member

22 Posts

Posted - 2006-09-21 : 13:19:40

sorry try this
select COLITM from proddta.f4105
where COLEDG in ('06','08' ) and COLEDG not in ('01','02','07')

Thanks,
Gopi Nath Muluka

misterzr
Starting Member

49 Posts

Posted - 2006-09-21 : 13:28:43

Gopi, I tried that first but it brings up all that have 06,08 in COLEDG. I need the COLITM that ONLY have 06 and 08 and no other (01,02,07) in COLEDG

Gopi Nath Muluka
Starting Member

22 Posts

Posted - 2006-09-21 : 13:31:53

Provide some sample data and structure of the table

Thanks,
Gopi Nath Muluka

misterzr
Starting Member

49 Posts

Posted - 2006-09-21 : 13:59:45

Here is an example of a number that has all 01-08 in the COLEDG column, what I am looking for is the numbers in COLITM that ONLY have a 06 or 08 in the COLEDG

COLITM COLEDG
137125 02
137125 07
137125 02
137125 07
137125 02
137125 07
137125 01
137125 08
137125 06
137125 02
137125 07
137125 02
137125 07
137125 02
137125 07

Gopi Nath Muluka
Starting Member

22 Posts

Posted - 2006-09-21 : 14:34:36

can you give us the desired output from the above sample

Thanks,
Gopi Nath Muluka

misterzr
Starting Member

49 Posts

Posted - 2006-09-21 : 15:08:06

This is more what I am after, if this was the whole table I am looking for the lines that would have the 100081 in the COLITM column, they only have a 06 and 08 in the COLEDG column and no 01,02,07 in the COLEDG column

COLITM COLEDG
137125 02
137125 07
137125 02
137125 07
137125 02
137125 07
137125 01
137125 08
137125 06
137125 02
137125 07
100081 06
100081 08
137125 02
137125 07

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-21 : 16:18:34

[code]declare @test table (COLITM int, COLEDG varchar(2))

insert @test
select 137125, '02' union all
select 137125, '07' union all
select 137125, '02' union all
select 137125, '07' union all
select 137125, '02' union all
select 137125, '07' union all
select 137125, '01' union all
select 137125, '08' union all
select 137125, '06' union all
select 137125, '02' union all
select 137125, '07' union all
select 100081, '06' union all
select 100081, '08' union all
select 137125, '02' union all
select 137125, '07'

select colitm
from @test
group by colitm
having max(case when coledg in ('06', '08') then 0 else 1 end) = 0[/code]
Peter Larsson
Helsingborg, Sweden

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-09-21 : 16:33:52

[code]Create table #t (COLITM varchar(20), COLEDG varchar(2))
Insert into #t values ('137125', '02')
Insert into #t values ('137125', '07')
Insert into #t values ('137125', '02')
Insert into #t values ('137125', '07')
Insert into #t values ('137125', '02')
Insert into #t values ('137125', '07')
Insert into #t values ('137125', '01')
Insert into #t values ('137125', '08')
Insert into #t values ('137125', '06')
Insert into #t values ('137125', '02')
Insert into #t values ('137125', '07')
Insert into #t values ('100081', '06')
Insert into #t values ('100081', '08')
Insert into #t values ('137125', '02')
Insert into #t values ('137125', '07')

Select * from #t where COLEDG in ('06','08') and COLITM not in
(Select COLITM from #t where COLEDG in ('01','02', '07'))[/code]

Srinika

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-21 : 16:39:39

I think using

Select * from @test where COLEDG in ('06','08') and COLITM not in
(Select COLITM from @test where COLEDG not in ('06','08'))

will be safer. Who knows if 01, 02 and 07 are the only other invalid possiblities?

Peter Larsson
Helsingborg, Sweden

misterzr
Starting Member

49 Posts

Posted - 2006-09-21 : 18:05:39

Thanks to all, Peso's solution does work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources