Recursive sql - I just don't get it

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 sql - I just don't get it

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-05-04 : 08:26:38

Hi people,

my problem is this: I have a table where I log logins to my site with userid and IP. A user can naturally log in many times and from several different ip's. I want to send in a userid to a procedure and get out all other userid's that can be connected to this ip or any other ip that are "interconnected" with this user.

Example: I send userid 14 to the procedure. This user has logged in 22 times with 6 different ip's. 2 of these ip's have been used by userid 126 that has logged in 250 times, and of his ip's 3 have been used by someone else (userid 87, 56 and 44). The last 3 users don't have any ip's connected with anyone else. My query based on this should return a total of 5 userid's because they are all "interconnected" by using the same ip's in some way.

Is this possible, and could someone push me in the right direction?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-04 : 08:35:41

well for each level you'll need another inner/left join.
because you probably don't know how deep does it go, you'll need to use dynamic sql to build the string...
or use a loop.

Go with the flow & have fun! Else fight the flow

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-05-04 : 08:45:01

I *think* this DDL should be right:

DECLARE @Table table (UserID int, IP varchar(15))

INSERT INTO @Table
SELECT 14,  '10.0.0.1' UNION ALL SELECT 126, '10.0.0.1' UNION ALL
SELECT 126, '10.0.0.2' UNION ALL SELECT 87,  '10.0.0.2' UNION ALL
SELECT 56,  '10.0.0.2' UNION ALL SELECT 44,  '10.0.0.2' UNION ALL
SELECT 126, '10.0.0.3' UNION ALL SELECT 126, '10.0.0.3' UNION ALL
SELECT 126, '10.0.0.4' UNION ALL SELECT 126, '10.0.0.4' UNION ALL
SELECT 14,  '10.0.0.5' UNION ALL SELECT 14,  '10.0.0.5' UNION ALL
SELECT 87,  '10.0.0.6' UNION ALL SELECT 11,  '10.0.0.7' UNION ALL
SELECT 74,  '10.0.0.7' UNION ALL SELECT 74,  '10.0.0.8' UNION ALL
SELECT 61,  '10.0.0.7' UNION ALL SELECT 61,  '10.0.0.8'

SELECT * FROM @Table

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-05-04 : 08:48:13

Hm, I was afraid I had to use a loop and go through it a fixed amount of times...I guess that was one of the reasons I posted. I'm no guru at this and I assumed you geniouses knew a better way

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources