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 > SQL Statement
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

isfaar
Starting Member

11 Posts

Posted - 10/05/2006 :  08:34:12  Show Profile  Reply with Quote
Thanks Peso.

It works fine...thanks a million.

But Now i Have a new issue. I need to find out total connections for each member (including all generations)

I have posted as a new topic 'Social Networking > Total Network Connections'

Please see the details there....I have also included it here just in case

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

Posted - 10/03/2006 : 08:55:22
--------------------------------------------------------------------------------

Ok, here it is anyway.
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'

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

Edited by - isfaar on 10/05/2006 08:51:49
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 10/05/2006 :  09:06:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This is answered here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73079


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2165 Posts

Posted - 10/05/2006 :  10:00:26  Show Profile  Reply with Quote
It seems we have found the new classroom problems (using social app instead of the old student/techer model).

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 01/08/2007 :  17:55:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by isfaar

I need to know if any knows how to implement Djikstra's Algorithm in MS SQL. If any knows please help. Peso do you know.
Here it is http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77262


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

my-sql
Starting Member

3 Posts

Posted - 03/07/2007 :  17:50:27  Show Profile  Reply with Quote
Hi,

how can I implement Dijkstra's Algorithm in MySQL... ?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 03/07/2007 :  17:54:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Copy and paste?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

my-sql
Starting Member

3 Posts

Posted - 03/08/2007 :  16:45:03  Show Profile  Reply with Quote
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@Want1 VARCHAR(2),
    @Want2 VARCHAR(2)
)
RETURNS INT
AS

BEGIN
    IF @Want1 ' at line 3

It will not work... :( MySQL does not use @...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 03/09/2007 :  03:42:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
So MySQL does not support functions?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

my-sql
Starting Member

3 Posts

Posted - 03/10/2007 :  05:55:58  Show Profile  Reply with Quote
It does But the syntax is different?
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 03/10/2007 :  10:10:15  Show Profile  Visit jezemine's Homepage  Reply with Quote
this forum is for sql server.

you are certainly free to take code examples from here and port them to mysql, but you are unlikely to find anyone to help you with that here.


www.elsasoft.org
Go to Top of Page

Swindla
Starting Member

USA
2 Posts

Posted - 03/25/2007 :  12:03:32  Show Profile  Reply with Quote
quote:
Originally posted by my-sql

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@Want1 VARCHAR(2),
    @Want2 VARCHAR(2)
)
RETURNS INT
AS

BEGIN
    IF @Want1 ' at line 3

It will not work... :( MySQL does not use @...



the "@" is used to denote a variable, so repleace the "@" with what ever MySql uses for variables.
Go to Top of Page

thecutter
Starting Member

4 Posts

Posted - 04/18/2007 :  11:15:13  Show Profile  Reply with Quote
First: Thx Peso, really nice Scripts!

But how can i do it to get all possible Paths from Person E to Person B?
I found your Dijkstra SQL Script but with it i only get one Path...
I'd like to have all possible Paths with a max distance of 5 nodes.
For Example: E-F-D-B / E-C-G-B / E-C-A-B

Is it possible with the Table Structure you defined earlier in this post!? And how fast will it be with many records in the database?

Edited by - thecutter on 04/18/2007 11:15:40
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 04/18/2007 :  12:38:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Try something like this
DECLARE	@Contacts TABLE (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'

DECLARE	@StartNode VARCHAR(2),
	@EndNode VARCHAR(2),
	@MaxNodes INT

SELECT	@StartNode = 'e',
	@EndNode = 'g',
	@MaxNodes = 5

DECLARE	@Stage TABLE (Grp INT, RecID INT IDENTITY(1, 1), PrevRecID INT, pFrom VARCHAR(2), pTo VARCHAR(2))

IF @MaxNodes > 1
	INSERT	@Stage (pFrom, pTo)
	SELECT	pFrom,
		pTo
	FROM	(
			SELECT	pFrom,
				pTo
			FROM	@Contacts
			WHERE	pFrom = @StartNode

			UNION ALL

			SELECT	pTo,
				pFrom
			FROM	@Contacts
			WHERE	pTo = @StartNode
		) AS x

UPDATE	@Stage
SET	Grp = RecID

WHILE @MaxNodes > 2 AND @@ROWCOUNT > 0
	BEGIN
		INSERT		@Stage (Grp, PrevRecID, pFrom, pTo)
		SELECT		s.Grp,
				s.RecID,
				x.pFrom,
				x.pTo
		FROM		@Stage AS s
		INNER JOIN	(
					SELECT	pFrom,
						pTo
					FROM	@Contacts

					UNION ALL

					SELECT	pTo,
						pFrom
					FROM	@Contacts
				) AS x ON x.pFrom = s.pTo
		WHERE		NOT EXISTS (SELECT * FROM @Stage AS e WHERE (x.pFrom = e.pFrom AND x.pTo = e.pTo OR x.pFrom = e.pTo AND x.pTo = e.pFrom) AND e.Grp = s.Grp)
				AND s.pTo <> @EndNode

		SET @MaxNodes = @MaxNodes - 1
	END

DELETE		s
FROM		@Stage AS s
LEFT JOIN	(
			SELECT	Grp
			FROM	@Stage
			WHERE	pTo = @EndNode
		) AS x ON x.Grp = s.Grp
WHERE		x.Grp IS NULL

DECLARE	@Route TABLE (Generation INT, Grp INT, PrevRecID INT, pFrom VARCHAR(2), pTo VARCHAR(2))

INSERT	@Route
SELECT	0,
	Grp,
	PrevRecID,
	pFrom,
	pTo
FROM	@Stage
WHERE	pTo = @EndNode

SELECT	@MaxNodes = 0

WHILE @@ROWCOUNT > 0
	BEGIN
		SET @MaxNodes = @MaxNodes + 1

		INSERT		@Route
		SELECT		@MaxNodes,
				s.Grp,
				s.PrevRecID,
				s.pFrom,
				s.pTo
		FROM		@Route AS r
		INNER JOIN	@Stage AS s ON s.RecID = r.PrevRecID
		WHERE		r.Generation = @MaxNodes - 1
	END

SELECT DISTINCT	Grp,
		COALESCE(PrevRecID, 0) AS RecID,
		pFrom,
		pTo
FROM		@Route
ORDER BY	Grp,
		2


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 04/18/2007 :  12:40:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Or this
DECLARE	@Contacts TABLE (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'

DECLARE	@StartNode VARCHAR(2),
	@EndNode VARCHAR(2),
	@MaxNodes INT

SELECT	@StartNode = 'e',
	@EndNode = 'g',
	@MaxNodes = 5

DECLARE	@Stage TABLE (Grp INT, RecID INT IDENTITY(1, 1), PrevRecID INT, pFrom VARCHAR(2), pTo VARCHAR(2))

IF @MaxNodes > 1
	INSERT	@Stage (pFrom, pTo)
	SELECT	pFrom,
		pTo
	FROM	(
			SELECT	pFrom,
				pTo
			FROM	@Contacts
			WHERE	pFrom = @StartNode

			UNION ALL

			SELECT	pTo,
				pFrom
			FROM	@Contacts
			WHERE	pTo = @StartNode
		) AS x

UPDATE	@Stage
SET	Grp = RecID

WHILE @MaxNodes > 2 AND @@ROWCOUNT > 0
	BEGIN
		INSERT		@Stage (Grp, PrevRecID, pFrom, pTo)
		SELECT		s.Grp,
				s.RecID,
				x.pFrom,
				x.pTo
		FROM		@Stage AS s
		INNER JOIN	(
					SELECT	pFrom,
						pTo
					FROM	@Contacts

					UNION ALL

					SELECT	pTo,
						pFrom
					FROM	@Contacts
				) AS x ON x.pFrom = s.pTo
		WHERE		NOT EXISTS (SELECT * FROM @Stage AS e WHERE (x.pFrom = e.pFrom AND x.pTo = e.pTo OR x.pFrom = e.pTo AND x.pTo = e.pFrom) AND e.Grp = s.Grp)
				AND s.pTo <> @EndNode

		SET @MaxNodes = @MaxNodes - 1
	END

DELETE		s
FROM		@Stage AS s
LEFT JOIN	(
			SELECT	Grp
			FROM	@Stage
			WHERE	pTo = @EndNode
		) AS x ON x.Grp = s.Grp
WHERE		x.Grp IS NULL

DECLARE	@Route TABLE (Generation INT, Grp INT, PrevRecID INT, pFrom VARCHAR(2), pTo VARCHAR(2))

INSERT	@Route
SELECT	0,
	Grp,
	PrevRecID,
	pFrom,
	pTo
FROM	@Stage
WHERE	pTo = @EndNode

SELECT	@MaxNodes = 0

WHILE @@ROWCOUNT > 0
	BEGIN
		SET @MaxNodes = @MaxNodes + 1

		INSERT		@Route
		SELECT		@MaxNodes,
				s.Grp,
				s.PrevRecID,
				s.pFrom,
				s.pTo
		FROM		@Route AS r
		INNER JOIN	@Stage AS s ON s.RecID = r.PrevRecID
		WHERE		r.Generation = @MaxNodes - 1
	END


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

SELECT		*
FROM		@Stage
ORDER BY	Grp,
		RecID


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

thecutter
Starting Member

4 Posts

Posted - 04/18/2007 :  14:10:54  Show Profile  Reply with Quote
THX Peso, the Script rockz!

But i think the second one doesn't work correct.
I don't see the last Node and the Path seems to be incorrect.

There is a last question:
How can I set the maximum Number of Paths to return?
I don't get an Idea where to set this in your Script...

//edit: I just tested your Script, but it seems they are very slow on a big database. The Script took 45 seconds on a Table with 120 Records.

Edited by - thecutter on 04/18/2007 15:23:38
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 04/19/2007 :  00:45:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Change the @MaxNodes value.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

thecutter
Starting Member

4 Posts

Posted - 04/19/2007 :  02:55:29  Show Profile  Reply with Quote
Yes i know the maxNodes value is for the number of nodes from person a to person b.
But i want to get only 3 possible Paths from person a to person b with a maximum of 5 nodes between them.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 04/19/2007 :  03:19:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DELETE r
FROM @Route as r
where r.grp not in (SELECT DISTINCT TOP 3 c.grp from @route as c)


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 04/19/2007 03:25:50
Go to Top of Page

thecutter
Starting Member

4 Posts

Posted - 04/19/2007 :  03:23:56  Show Profile  Reply with Quote
Ok thx, i will test it! =)
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 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.27 seconds. Powered By: Snitz Forums 2000