Social Networking > Total Network Connections

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)

Social Networking > Total Network Connections

Author

Topic

isfaar
Starting Member

11 Posts

Posted - 2006-10-05 : 08:31:21

Hi,

I need to find out total connections (in all generations) of a member in a social network. Is there any solution with SQL Functions.

TABLE members(mFrom VARCHAR(2), mTo VARCHAR(2))

mFrom mTo
--------------------------
'A' 'B'
'B' 'D'
'C' 'A'
'C' 'E'
'G' 'C'
'B' 'G'
'F' 'D'
'E' 'F'

-- This is the node chart of connections
/*
A - B
/ / \
C - G D
\ /
E - F
*/

For ex : I need to find all connections for member A
According to above node diagram It Should Show
Direct Connections (First Degree Connections) : 2
Second Degree Connections : 4
Total Connections : 2 + 4 + 2 (One Each for 'E' And 'D', 'F' is common to 'D' And 'E')

Any help is greatly appreciated. PESO please help. Here is the sample data.

Regards
Isfaar

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-05 : 08:35:50

Have a look at this:

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72097[/url]

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-05 : 08:41:04

Are you counting connections, or common friends?

Peter Larsson
Helsingborg, Sweden

isfaar
Starting Member

11 Posts

Posted - 2006-10-05 : 08:53:12

counting connections

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-05 : 09:02:07

[code]CREATE TABLE Contacts (pFrom VARCHAR(2), pTo VARCHAR(2))

INSERT Contacts
SELECT 'A', 'B' UNION ALL
SELECT 'B', 'D' UNION ALL
SELECT 'C', 'A' UNION ALL
SELECT 'C', 'E' UNION ALL
SELECT 'G', 'C' UNION ALL
SELECT 'B', 'G' UNION ALL
SELECT 'F', 'D' UNION ALL
SELECT 'E', 'F'

-- This is the node chart of connections
/*

A - B
/ / \
C - G D
\ /
E - F
*/

select * from contacts

select pFrom p, dbo.fnFriendsStep(pfrom) from contacts
union
select pTo, dbo.fnFriendsStep(pto) from contacts

drop table contacts

CREATE FUNCTION dbo.fnFriendsStep
(
@Want VARCHAR(2)
)
RETURNS INT
AS

BEGIN
IF @Want IS NULL
RETURN -1

DECLARE @Friends TABLE (Generation INT, p VARCHAR(2))
DECLARE @Generation INT

SELECT @Generation = 0

INSERT @Friends
(
Generation,
p
)
SELECT 0,
pFrom
FROM Contacts
WHERE pTo = @Want
UNION
SELECT 0,
pTo
FROM Contacts
WHERE pFrom = @Want
UNION
SELECT -1,
@Want

WHILE @@ROWCOUNT > 0
BEGIN
SELECT @Generation = @Generation + 1

INSERT @Friends
(
Generation,
p
)
SELECT @Generation,
pFrom
FROM Contacts
WHERE pTo IN (SELECT p FROM @Friends WHERE Generation = @Generation - 1)
AND pFrom NOT IN (SELECT p FROM @Friends)
UNION ALL
SELECT @Generation,
pTo
FROM Contacts
WHERE pFrom IN (SELECT p FROM @Friends WHERE Generation = @Generation - 1)
AND pTo NOT IN (SELECT p FROM @Friends)
END
RETURN (SELECT COUNT(*) FROM @Friends WHERE Generation >= 0)
END[/code]

Peter Larsson
Helsingborg, Sweden

isfaar
Starting Member

11 Posts

Posted - 2006-10-06 : 08:01:33

Thanks Peter. Thanks a million. I have a doubt though. Do you think this script will stand for a large database of contacts. I mean will the Sql Server not give an error if the loop executes more than 32 times?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-09 : 03:12:16

No, it will not give an error.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-26 : 04:28:01

[code]CREATE FUNCTION dbo.fnFriendsBetween
(
@From VARCHAR(2),
@To VARCHAR(2)
)
RETURNS INT
AS

BEGIN
IF @From IS NULL OR @To IS NULL
RETURN -1

DECLARE @Friends TABLE (Generation INT, p VARCHAR(2))
DECLARE @Generation INT

SELECT @Generation = 0

INSERT @Friends
(
Generation,
p
)
SELECT 0,
@From

WHILE @@ROWCOUNT > 0 AND NOT EXISTS (SELECT * FROM @Friends WHERE p = @To)
BEGIN
SELECT @Generation = @Generation + 1

INSERT @Friends
(
Generation,
p
)
SELECT @Generation,
pTo
FROM Contacts
WHERE pFrom IN (SELECT p FROM @Friends WHERE Generation = @Generation - 1)
AND pTo NOT IN (SELECT p FROM @Friends)
UNION ALL
SELECT @Generation,
pFrom
FROM Contacts
WHERE pTo IN (SELECT p FROM @Friends WHERE Generation = @Generation - 1)
AND pFrom NOT IN (SELECT p FROM @Friends)
END

RETURN @Generation
END[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources