Query of two inter-related fields within a table

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)

Query of two inter-related fields within a table

Author

Topic

ken2005
Starting Member

2 Posts

Posted - 2005-06-17 : 04:43:36

Hi Everyone!

I am a newbie here trying out some database query problems. I am stuck and need some help. Here's the problem:-

I've two fields in a table; field A & field B

A B
1 Null
2 Null
3 1
4 2
5 3
6 5
7 Null
8 4
9 6

The fields are somehow related where values of B (unique) is taken from A(Autonumber-unique).
I am trying to do a parameter query such that if I input a single value for A say, 9 or 6 or 5 or 3 or 1, the query will do a search for correspondent value B, and with the B value it will now search for new A value and repeat until all related records are retrieve. The following should be the output:

A B
9 6
6 5
5 3
3 1
1 Null

In similar case where I input a single value A say, 8 or 4 or 2
the following should be the output:

A B
8 4
4 2
2 Null

Any idea of how to do that? Many thanks in advance!!

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-06-17 : 10:02:15

Maybe I do not quite understand but isnt this a simple select.

CREATE PROC getMyData
@parameterstring varch(100)
AS
SELECT A,B
FROM yourtable
WHERE A in (@parameterstring)

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-17 : 14:40:49

I think you might be looking for something like this:


create proc dbo.getMyData (@a int)
as
set nocount on
set ansi_warnings off

begin
	set nocount on
	declare @b int
	declare @results table (a int, b int)

	set @b = (select min(b) from test where a = @a)

	while @b is not null
	begin
		insert into @results 
			select @a, @b
	
		set @a = @b
		select @b = min(b) from test where a = @b
	end
	select * from @results

end
go

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-17 : 14:51:55

quote:
Originally posted by Thrasymachus

Maybe I do not quite understand but isnt this a simple select.

CREATE PROC getMyData
@parameterstring varch(100)
AS
SELECT A,B
FROM yourtable
WHERE A in (@parameterstring)

You can't do that! Check this out for details:
http://www.sqlteam.com/item.asp?ItemID=11499

Tara

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-06-17 : 15:10:18

you are right. i have a copy of Brett's(?) function too. Slipped my mind.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-17 : 15:13:02

Tara,
Is it bad practice to turn ansi warnings off to avoid those "Aggregate function eliminated a NULL value..." errors, that you encounter in a situation like this... or at least that I ran into in the proc I posted above? Do you see another way to write the proc to avoid the error?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-17 : 15:15:25

I haven't ever used the ansi_warnings options as I just ignore the warnings.

Tara

ken2005
Starting Member

2 Posts

Posted - 2005-06-19 : 22:41:54

Thank you ALL! I will try it out in a while.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources