SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Social Networking > Total Network Connections
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

isfaar
Starting Member

11 Posts

Posted - 10/05/2006 :  08:31:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 10/05/2006 :  08:35:50  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Have a look at this:

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

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 10/05/2006 :  08:41:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Are you counting connections, or common friends?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

isfaar
Starting Member

11 Posts

Posted - 10/05/2006 :  08:53:12  Show Profile  Reply with Quote
counting connections
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 10/05/2006 :  09:02:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

isfaar
Starting Member

11 Posts

Posted - 10/06/2006 :  08:01:33  Show Profile  Reply with Quote
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?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 10/09/2006 :  03:12:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
No, it will not give an error.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 03/26/2007 :  04:28:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000