/
Graph_4_QueryEmployees_SQL_Server.sql
238 lines (210 loc) · 7.64 KB
/
Graph_4_QueryEmployees_SQL_Server.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
/*
Adding Graph Structures - Hierarchy Queries Graph
Steve Jones, copyright 2022
This code is provided as is for demonstration purposes. It may not be suitable for
your environment. Please test this on your own systems. This code may not be republished
or redistributed by anyone without permission.
You are free to use this code inside of your own organization.
*/
USE Northwind
GO
DROP TABLE IF EXISTS GraphEmployees;
GO
CREATE TABLE GraphEmployees (
EmpID INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
Title VARCHAR(50)
) AS NODE;
INSERT INTO GraphEmployees (FirstName, LastName, Title)
SELECT FirstName,
LastName,
Title
FROM dbo.Employees
GO
SELECT top 20
*
FROM dbo.GraphEmployees
GO
/*
{"type":"node","schema":"dbo","table":"GraphEmployees","id":1}
*/
-- create edge
DROP TABLE IF EXISTS ReportsTo;
GO
CREATE TABLE ReportsTo (as_of DATE DEFAULT GETDATE()) AS EDGE;
-- properties?
/* Use this hierarchy
11 Tia Jones 10 Steve Jones
10 Steve Jones 2 Andrew Fuller
2 Andrew Fuller 1 Nancy Davolio
2 Andrew Fuller 3 Janet Leverling
2 Andrew Fuller 4 Margaret Peacock
2 Andrew Fuller 5 Steven Buchanan
2 Andrew Fuller 8 Laura Callahan
5 Steven Buchanan 6 Michael Suyama
5 Steven Buchanan 7 Robert King
5 Steven Buchanan 9 Anne Dodsworth
*/
INSERT INTO ReportsTo ($from_id, $to_id, as_of) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 10),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 11), '2023-01-01');
INSERT INTO ReportsTo ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 2),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 10));
INSERT INTO ReportsTo ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 1),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 2));
INSERT INTO ReportsTo ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 3),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 2));
INSERT INTO ReportsTo ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 4),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 2));
INSERT INTO ReportsTo ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 5),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 2));
INSERT INTO ReportsTo ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 8),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 2));
INSERT INTO ReportsTo ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 6),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 5));
INSERT INTO ReportsTo ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 7),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 5));
INSERT INTO ReportsTo ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 9),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 5));
GO
SELECT * FROM dbo.ReportsTo AS rt
GO
/*
{"type":"edge","schema":"dbo","table":"ReportsTo","id":0}
{"type":"node","schema":"dbo","table":"GraphEmployees","id":9}
{"type":"node","schema":"dbo","table":"GraphEmployees","id":10}
*/
SET STATISTICS IO ON
SET STATISTICS TIME ON
-- Who reports to whom
SELECT emp1.EmpID, emp1.FirstName AS Employee, emp1.Title, emp2.EmpID, emp2.FirstName as Manager, emp2.Title
FROM GraphEmployees emp1, ReportsTo, GraphEmployees emp2
WHERE MATCH(emp1-(ReportsTo)->emp2)
ORDER BY Employee, Manager;
GO
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO
DROP TABLE IF EXISTS WorksWith
GO
CREATE TABLE WorksWith AS EDGE;
/* Use this linkage
11 Tia 10 Steve
8 Laura 5 Steven
1 Nancy 3 Janet
8 Laura 2 Andrew
7 Robert 9 Anne
8 Laura 3 Janet
*/
INSERT INTO WorksWith ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 10),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 11));
INSERT INTO WorksWith ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 8),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 5));
INSERT INTO WorksWith ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 1),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 3));
INSERT INTO WorksWith ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 2),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 8));
INSERT INTO WorksWith ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 7),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 9));
INSERT INTO WorksWith ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 8),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 3));
-- Also need reverse relationship
INSERT INTO WorksWith ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 11),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 10));
INSERT INTO WorksWith ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 5),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 8));
INSERT INTO WorksWith ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 8),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 3));
INSERT INTO WorksWith ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 8),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 2));
INSERT INTO WorksWith ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 9),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 7));
INSERT INTO WorksWith ($from_id, $to_id) VALUES (
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 3),
(SELECT $node_id FROM GraphEmployees WHERE EmpID = 8));
GO
-- Query for data
-- Who works with whom
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT emp1.FirstName + ', ' + emp1.Title AS Employee,
emp2.FirstName + ', ' + emp2.Title as CoWorker
FROM GraphEmployees emp1, WorksWith, GraphEmployees emp2
WHERE MATCH(emp1-(WorksWith)->emp2)
ORDER BY Employee, CoWorker;
GO
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
-- query coworkers
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT e.FirstName + ', ' + e.Title AS Employee
, cw.Relationship
, e2.FirstName + ', ' + e2.Title AS Coworker
FROM dbo.Employees AS e
INNER JOIN dbo.CoWorker AS cw
ON e.EmployeeID = cw.EmployeeID
AND cw.Relationship = 'Works With'
INNER JOIN dbo.Employees AS e2
ON cw.EmployeeID2 = e2.EmployeeID
UNION
SELECT e2.FirstName + ', ' + e.Title AS Employee
, cw.Relationship
, e.FirstName + ', ' + e2.Title AS Coworker
FROM dbo.Employees AS e
INNER JOIN dbo.CoWorker AS cw
ON e.EmployeeID = cw.EmployeeID
AND cw.Relationship = 'Works With'
INNER JOIN dbo.Employees AS e2
ON cw.EmployeeID2 = e2.EmployeeID
ORDER BY Employee, CoWorker
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO
SET STATISTICS IO ON
SET STATISTICS TIME ON
-- Find potential conflicts
-- Who works with whom and reports to them
SELECT emp1.FirstName + ', ' + emp1.Title AS Employee,
emp2.FirstName + ', ' + emp2.Title as Manager
FROM GraphEmployees emp1, WorksWith, GraphEmployees emp2, dbo.ReportsTo
WHERE MATCH(emp1-(WorksWith)->emp2)
AND MATCH(emp1-(ReportsTo)->emp2)
ORDER BY Employee, Manager;
GO
-- Who works with whom and reports to them
SELECT e.FirstName + ', ' + e.Title AS Employee
, cw.Relationship
, e2.FirstName + ', ' + e2.Title AS Coworker
FROM dbo.Employees AS e
INNER JOIN dbo.CoWorker AS cw
ON e.EmployeeID = cw.EmployeeID
AND cw.Relationship = 'Works With'
INNER JOIN dbo.Employees AS e2
ON cw.EmployeeID2 = e2.EmployeeID
WHERE e.ReportsTo = e2.EmployeeID
ORDER BY Employee, Coworker;
GO
SET STATISTICS IO OFF
SET STATISTICS TIME OFF