Another tricky query. - 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)

Another tricky query.

Author

Topic

Juason
Starting Member

17 Posts

Posted - 2006-05-26 : 15:58:25

I currently have a query that returns this :

Id, Step, SubStep Type
0, 100, 0, Default
1, 200, 0, Default
2, 200, 1, Added
3, 200, 2, Added
4, 300, 0, Default
5, 300, 0, Added
6, 300, 1, Added
7, 400, 0, Default

This represents a series of steps, build on the fly using several parameters. My goal is to build-in a way for people to SKIP a step. To do this I must return the following:

Id, Step, SubStep Type
0, 100, 0, Default
1, 200, 0, Default
2, 200, 1, Added
3, 200, 2, Added
5, 300, 0, Added
6, 300, 1, Added
7, 400, 0, Default

The Default steps are always there, unless proceeded by an Added step.

Id, Step, SubStep Type
4, 300, 0, Default
5, 300, 0, Added

This is taken from the above example, and what I need to do is select the Added Type and not the Default Type, when an Added type has the same Step and SubStep numbers as a Default Type.

Does anyone know a way for a query to do this? I apologize for the complexity.

Juason
Starting Member

17 Posts

Posted - 2006-05-26 : 16:02:13

I should have mentioned, the query that returns the above dataset is as follows:

SELECT * from dbo.ActionDef
WHERE Type ='Default' or Type = 'Added')
ORDER by Step, SubStep

Juason
Starting Member

17 Posts

Posted - 2006-05-26 : 17:04:20

Heh, seems like this is my project for today... ok so here was my thoughts:

select * into #table2
from dbo.actiondef where Type = 'Default'
Order by Step, SubStep

select * into #table3
from dbo.actiondef where Type = 'Added'
Order by Step, SubStep

insert into #table3 (Step, SubStep, Type)
Select Step, SubStep, Type from #table2
Where (#tabel2.Step <> #table3.Step and #table2.SubStep <> #table3.SubStep)
Order by SeqNum, SubSeqNum

The final contents of #table3 would be what I want - if the last insert query functioned properly.

It would be cool to find a single query to extract what I want, because the asp.net application I am using only allows 1 query per datasource - so doing temp tables isn't easy. Well, if anyone happens to think of something over the long holiday weekend I'd be most appreciative!

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-26 : 17:21:43

Try the following


Select Q1.Step, Q1.SubStep, Q1.Type from 
(select * from dbo.actiondef where Type = 'Default' ) as Q1
inner join 
(select * from dbo.actiondef where Type = 'Added' ) as Q2
on Q1.id = Q2.id
Where (Q1.Step <> Q2.Step and Q1.SubStep <> Q2.SubStep)
Order by Q1.SeqNum, 1.2SubSeqNum

Srinika

Juason
Starting Member

17 Posts

Posted - 2006-05-30 : 09:41:55

Well, the specifics of the project have changed slightly. Is there any way to take the data set I posted above, and return all values EXCEPT for rows with repeating STEP and SUBSTEP numbers?

Id, Step, SubStep Type
0, 100, 0, Default
1, 200, 0, Default
2, 200, 1, Added
3, 200, 2, Added
4, 300, 0, Default*
5, 300, 0, Added*
6, 300, 1, Added
7, 400, 0, Default

So, for the data set above I need a query that returns all rows EXCEPT those I have indicated with a *. Thank you again for any help you can give me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources