| Author |
 Topic  |
|
isfaar
Starting Member
11 Posts |
 Posted - 10/05/2006 : 08:34:12
|
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 |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2113 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 01/08/2007 : 17:55:09
|
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 |
|
|
|
my-sql
Starting Member
3 Posts |
Posted - 03/07/2007 : 17:50:27
|
Hi,
how can I implement Dijkstra's Algorithm in MySQL... ? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 03/07/2007 : 17:54:50
|
Copy and paste?
Peter Larsson
Helsingborg, Sweden |
|
|
|
my-sql
Starting Member
3 Posts |
Posted - 03/08/2007 : 16:45:03
|
#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 @... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 03/09/2007 : 03:42:33
|
So MySQL does not support functions? 
Peter Larsson
Helsingborg, Sweden |
|
|
|
my-sql
Starting Member
3 Posts |
Posted - 03/10/2007 : 05:55:58
|
It does  But the syntax is different?  |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2875 Posts |
Posted - 03/10/2007 : 10:10:15
|
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 |
|
|
|
Swindla
Starting Member
USA
2 Posts |
Posted - 03/25/2007 : 12:03:32
|
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. |
|
|
|
thecutter
Starting Member
4 Posts |
Posted - 04/18/2007 : 11:15:13
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 04/18/2007 : 12:38:16
|
Try something like thisDECLARE @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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 04/18/2007 : 12:40:47
|
Or thisDECLARE @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 |
|
|
|
thecutter
Starting Member
4 Posts |
Posted - 04/18/2007 : 14:10:54
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 04/19/2007 : 00:45:59
|
Change the @MaxNodes value.
Peter Larsson
Helsingborg, Sweden |
|
|
|
thecutter
Starting Member
4 Posts |
Posted - 04/19/2007 : 02:55:29
|
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. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 04/19/2007 : 03:19:55
|
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 |
|
|
|
thecutter
Starting Member
4 Posts |
Posted - 04/19/2007 : 03:23:56
|
| Ok thx, i will test it! =) |
|
|
|
Topic |
|
|
|
Social Networking > SQL Statement
