Recursive join, or something else? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Recursive join, or something else?

Author

Topic

StephanB
Starting Member

1 Post

Posted - 2004-10-03 : 06:35:04

Hi all,

I was wondering if someone could help with the following problem. I have got a table which looks (simplified) like this:

FLD_ID (Identity field autonum)
FLD_BaseID (Integer)
FLD_RefID

I want to add records in a chain, so when adding records, the first insert adds a record to this table. The new generated ID of the FLD_ID field is used in the other inserts following the firt one. The value of de FLD_ID is then used in the FLD_BaseID field. i.e.

Rec.nr FLD_ID FLD_BaseID FLD_RefID
1 4 0 2
2 5 4 2
3 6 4 2
4 7 4 2
5 8 0 2
6 9 4 2
7 10 8 2

In this case the records 1,2,3,4 and 6 belong together. The first record (1) is the base record, the others (2,3,3,4,6) belong to the first record. Now I'm looking for a query which will give me the results in the right order. In this case the result should be like this: 1,2,3,4,6,5 <- The first records belong together. The problem is that the "chain" doesn't have to be in this order, that's why I added new records (5,7) to it. At this moment I have this query:

SELECT *
FROM TBL_Result AS T1
LEFT JOIN TBL_Result AS T2 ON T1.FLD_ID = T2.FLD_BaseID AND T1.FLD_BaseID = 0
WHERE T1.FLD_RefID = 2
ORDER BY T1.FLD_ID,T2.FLD_ID

The problem is that it returns more records than needed. In this case is would return:
T1.FLD_ID T1.FLD_BaseID T1.FLD_RefID T2.FLD_ID
4 0 2 5
4 0 2 6
4 0 2 7
4 0 2 9
5 4 2 NULL
6 4 2 NULL
7 4 2 NULL
9 4 2 NULL
8 0 2 10
10 8 2 NULL

As you can see every record is double, in the programmerscode I can work around this, but maybe there is a more sufficient query. I would like to have a result like this

T1.FLD_ID T1.FLD_BaseID T1.FLD_RefID
4 0 2
5 4 2
6 4 2
7 4 2
9 4 2
8 0 2
10 8 2

It's a long story, but I hope someone knows what I mean and can help me with this.

THX

Stephan

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-05 : 08:40:00

SELECT CASE WHEN T1.FLD_ID=T2.FLD_ID THEN T1.FLD_ID ELSE T2.FLD_ID END FLD_ID,
CASE WHEN T1.FLD_ID=T2.FLD_ID THEN T1.FLD_BaseID ELSE T2.FLD_BaseID END FLD_BaseID, T1.FLD_RefID
FROM TBL_Result T1 JOIN TBL_Result T2 ON (T1.FLD_ID = T2.FLD_BaseID OR T1.FLD_ID = T2.FLD_ID) AND T1.FLD_BaseID = 0
WHERE T1.FLD_RefID = 2
ORDER BY T1.FLD_ID

Weevil
Starting Member

5 Posts

Posted - 2004-10-05 : 17:04:24

Maybe I'm looking at this sideways, but is the wildcard killing you? Can you use:

select distinct T1.FLD_ID T1.FLD_BaseID T1.FLD_RefID
FROM TBL_Result AS T1
LEFT JOIN TBL_Result AS T2 ON T1.FLD_ID = T2.FLD_BaseID AND T1.FLD_BaseID = 0
WHERE T1.FLD_RefID = 2
ORDER BY T1.FLD_ID,T2.FLD_ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources