Returning all linked values in a self-linked table

[glyph1]

Database: MSDE SQL Server 2000

Table Contract

ContractID PK*
ContractNumber
Status
NextContractID FK*

I am looking to select [ContractNumber] based on an input parameter and return all (non-null) of the linked [ContractNumber]s based on the [NextContractID] / [ContractID] relationship. I am trying to establish this in a single sql query.

I could not figure out a way to do this, any help would be greatly appreciated!

 

[gmmastros]

Try...

Select A.ContractNumber, B.ContractNumber As NextContractNumber
From   Contract As A
       Inner Join Contract As B
         On A.NextContractId = B.ContractId

It's hard to tell if this is what you're looking for. If it is, and you would like me to explain it, just let me know.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[glyph1]

Thank you very much George for your quick response.

Basically I am looking to Select every ContractNumber that is linked to a starting point: a @parameter. I wanted to create something that is similar to a linked list.

something like this link:

http://www.orafaq.com/usenet/comp.databases.theory/2003/08/21/0101.htm

 

[glyph1]

Just following up to my original post, I created a stored procedure using a while loop to achieve this.

CREATE Procedure get_contracts
	@ContractID int
AS 
BEGIN

DECLARE @NextID int
DECLARE @ContractTable TABLE -- Temporary Table
	(ContractID INT, NextContractID INT, ContractNumber varchar(20))

SET @NextID = @ContractID -- For first interation only

	-- Begin Insert Previous Into TempTable
	INSERT @ContractTable 

	SELECT ContractID, NextContractID, ContractNumber
	FROM Contract
	WHERE NextContractID = @ContractID
	-- End Insert Previous

	-- Begin Insert Next Into TempTable
	WHILE (@NextID IS NOT NULL) 
	BEGIN
		INSERT @ContractTable

		SELECT ContractID, NextContractID, ContractNumber
		FROM Contract
		WHERE ContractID = @NextID AND ContractID != @ContractID -- Omit parameter value

		SET @NextID = (Select NextContractID FROM Contract Where ContractID = @NextID)
	END;
	-- End Insert Next

END;

SELECT * From @ContractTable

 

[fredericofonseca]

if you were working with 2005, Recursive Common Table Expressions would give you what you want easily. Will you be upgrading soon? if so look at them.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886