I have to count edges between neighbors of nodes in a graph in SQL Server whereas I have tables as GraphNodes and GraphEdges. The structure of tables is available in previous question
Relating to previous question, here is the aspect of question is different as:
I have to execute these steps to perform the task i.e.
take a node say V from
GraphNodeshave to have
DISTINCTneighbors list for V (e.g. in a TABLE variable SQL)- check (COUNT DISTINCT) links between neighbors of V in
GraphEdges - output V with DISTINCT links between its neighbors
The query I have tried for a single node works fine i.e.
SELECT GN.id, COUNT(DISTINCT(CONCAT(GE.Source_Node,'-', GE.Target_Node))) AS NeighborLinks
FROM GraphEdges GE
JOIN GraphNodes GN ON GN.id = 512
WHERE Source_Node IN (SELECT DISTINCT Target_Node FROM GraphEdges WHERE Source_Node = 512
UNION ALL
SELECT DISTINCT Source_Node FROM GraphEdges WHERE Target_Node = 512
)
AND Target_Node IN (SELECT DISTINCT Target_Node FROM GraphEdges WHERE Source_Node = 512
UNION ALL
SELECT DISTINCT Source_Node FROM GraphEdges WHERE Target_Node = 512
)
GROUP BY GN.id
I have taken id = 512 as a sample where it is id in GraphNodes. This query outputs as:
+-------+-----------------+
| id | NeighborLinks |
+-------+-----------------+
| 512 | 6 |
+-------+-----------------+
The reason for using UNION ALL in WHERE clause is that the id i.e. 512 exists in both columns i.e. Source_Node and Target_Node as well, so have to select DISTINCT neighbors from both columns is necessary. Moreover, using same list for GE.Source_Node and GE.Target_Node because have to check links only between neighbors of V i.e. 512.
The question is how to use what I think the TABLE variable or any other method to sort out this problem of providing long list of values instead of 512
I have came up with this solution regarding table variable but got error using table variables inside query as:
Try 1
DECLARE @ID TABLE(id INT)
DECLARE @S_Neighbor TABLE (id INT)
DECLARE @T_Neighbor TABLE (id INT)
INSERT INTO @ID SELECT id FROM GraphNodes
INSERT INTO @S_Neighbor SELECT DISTINCT Source_Node
FROM GraphEdges
WHERE Target_Node IN (SELECT id FROM @ID)
--UNION ALL
INSERT INTO @T_Neighbor SELECT DISTINCT Target_Node
FROM GraphEdges
WHERE Source_Node IN (SELECT id FROM @ID)
SELECT GN.id,COUNT(DISTINCT(CONCAT(GE.Source_Node,'-', GE.Target_Node))) AS Mutual_Links
FROM GraphEdges GE
JOIN GraphNodes GN ON GN.id = @ID
WHERE Source_Node IN (SELECT DISTINCT Target_Node
FROM GraphEdges
WHERE Source_Node IN @T_Neighbor
UNION ALL
SELECT DISTINCT Source_Node
FROM GraphEdges
WHERE Target_Node IN @S_Neighbor)
AND Target_Node IN (SELECT DISTINCT Target_Node
FROM GraphEdges
WHERE Source_Node IN @S_Neighbor
UNION ALL
SELECT DISTINCT Source_Node
FROM GraphEdges
WHERE Target_Node IN @T_Neighbor)
GROUP BY GN.id
I also tried this:
Try 2
DECLARE @ID_COUNTER INT
DECLARE @MAX_ID INT
SET @ID_COUNTER = 1
SET @MAX_ID = 148410
WHILE @ID_COUNTER <= @MAX_ID
BEGIN
(
SELECT GN.id,
COUNT(DISTINCT(CONCAT(GE.Source_Node,'-', GE.Target_Node))) AS Mutual_Links
FROM GraphEdges GE
JOIN GraphNodes GN ON GN.id = @ID_COUNTER
WHERE Source_Node IN (SELECT DISTINCT Target_Node
FROM GraphEdges WHERE Source_Node = @ID_COUNTER
UNION ALL
SELECT DISTINCT Source_Node
FROM GraphEdges WHERE Target_Node = @ID_COUNTER
)
AND Target_Node IN (SELECT DISTINCT Target_Node
FROM GraphEdges WHERE Source_Node = @ID_COUNTER
UNION ALL
SELECT DISTINCT Source_Node
FROM GraphEdges WHERE Target_Node = @ID_COUNTER
)
GROUP BY GN.id
)
SET @ID_COUNTER += 1
END
I have used @MAX_ID = 3 and it took 56 sec to return output whereas @MAX_ID is originally = 148410. Though the returned values for NeighborLinks are correct but the output shown is in three separate windows for as:
id NeighborLinks
1 53
id NeighborLinks
2 318
id NeighborLinks
3 297
WHERE Source_Node IN @T_Neighboryou should writeWHERE Source_Node IN (SELECT ID FROM @T_Neighbor AS TN).JOIN GraphNodes GN ON GN.id = @IDis also wrong and it is completely unclear to me what you are trying to achieve here.