Run down a list in SQL - 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

General SQL Server Forums

New to SQL Server Programming

Run down a list in SQL

Author

Topic

Inno
Starting Member

33 Posts

Posted - 2006-11-21 : 02:55:42

I have tilted and need some help.

I have a table with columns:

ArtNo, WareNo, State + a few others

Sample data:

111, 1, 3
111, 2, null
111, 4, 10
222, 1, 8
222, 2, 3
222, 1, 3
and so on

Each artNo is in several WareNo and has a state
Each WareNo has several ArtNo

I want to run through the table and get * for each combination of ArtNo&WareNo that does not have any post in the table with State = 3.

Best regards

Inno

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 03:00:14

select distinct artno, wareno from sampletable
where state <> 3 or state is null

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 03:02:59

select distinct e.artno, e.wareno
from sampletable e
left join (select artno, wareno from sampletable where state = 3) q on q.artno = e.artno and q.wareno = e.wareno
where q.artno is null

select distinct e.artno, e.wareno
from sampletable e
left join (select artno, wareno from sampletable where state = 3 or state is null) q on q.artno = e.artno and q.wareno = e.wareno
where q.artno is null

Peter Larsson
Helsingborg, Sweden

Inno
Starting Member

33 Posts

Posted - 2006-11-21 : 03:07:13

I dont get it...I want every combination of ArtNo and WareNo which doesnt have the state 3 in any line. The state kan be several values which I cant specify.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 03:09:47

select distinct artno, wareno from sampletable
where state not in (3,x,y,z)

???

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 03:10:34

How about you post the expected output based on the sample data provided above?

Peter Larsson
Helsingborg, Sweden

Inno
Starting Member

33 Posts

Posted - 2006-11-21 : 03:13:36

Good idea :)

The result I want from above is:

111, 2, null
111, 4, 8

None of theese combinations of artNo and wareNo have any line with state 3

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 03:17:23

What's wrong with the second answer I gave you?

-- prepare test data
declare @test table (artno int, wareno int, state int)

insert	@test
select	111, 1, 3 union all
select	111, 2, null union all
select	111, 4, 10 union all
select	222, 1, 8 union all
select	222, 2, 3 union all
select	222, 1, 3

-- do the work
select distinct e.artno, e.wareno, e.state
from @test e
left join (select artno, wareno from @test where state = 3) q on q.artno = e.artno and q.wareno = e.wareno
where q.artno is null

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 03:25:06

Also this works

-- do the work
select distinct e.artno, e.wareno, e.state
from @test e
where not exists (select * from @test where state = 3 and artno = e.artno and wareno = e.wareno)

However, the suggestion with left join has slightly better performance.

Peter Larsson
Helsingborg, Sweden

Inno
Starting Member

33 Posts

Posted - 2006-11-21 : 03:37:19

Yah it works, Thatx alot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources