· 7 years ago · Sep 02, 2018, 03:14 PM
1SQL Server - Selecting list of keywords and synonymous
2CREATE TABLE [dbo].[Keywords]
3[KeywordID] [int] IDENTITY(1,1) NOT NULL,
4[Description] [varchar](200) NOT NULL
5
6select * from Keywords
7
8 1 MVC
9 2 HTML
10 3 C#
11 4 ASP.NET MVC
12 5 MVC3
13
14CREATE TABLE [dbo].[KeywordSynonymous]
15 [KeywordID] [int] NOT NULL,
16 [KeywordSynonymousID] [int] NOT NULL
17
18select * from KeywordSynonymous
19
201 5
215 4
22
23>> if the user types 'MVC', my sql should return 'MVC, MVC3', 'ASP.NET MVC'.
24>> if the user types 'MVC3', my sql should return 'MVC, MVC3', 'ASP.NET MVC'.
25>> if the user types 'ASP.NETMVC', my sql should return 'MVC, MVC3', 'ASP.NET MVC'.
26
27select * from KeywordSynonymous
28
291 5
305 4
31
32select * from KeywordSynonymousAll
33
341 5 0
352 NULL 0
363 NULL 0
374 NULL 0
384 5 1
395 1 1
405 4 0
41
42create view KeywordSynonymousAll as
43 select KeywordID, KeywordSynonymousID, 0 as reversed
44 from KeywordSynonymous
45 union
46 select K.KeywordID, null as KeywordSynonymousID, 0 as reversed
47 from Keywords K
48 where not exists(select null
49 from KeywordSynonymous
50 where KeywordID = K.KeywordID)
51 union
52 select KeywordSynonymousID, KeywordID, 1 as reversed
53 from KeywordSynonymous
54
55declare @search varchar(200);
56
57set @search = 'MVC3'; -- TEST HERE for different search keywords
58
59with Synonymous (keywordID, SynKeywordID) as (
60
61 -- initial state: Get the keywordId and KeywordSynonymousID for the description as @search
62 select K.keywordID, KS.KeywordSynonymousID
63 from Keywords K
64 inner join KeywordSynonymous KS on KS.KeywordID = K.keywordId
65 where K.Description = @search
66
67 union all
68
69 -- also initial state but with reversed columns (because we want lookup in both directions)
70 select KS.KeywordSynonymousID, K.keywordID
71 from Keywords K
72 inner join KeywordSynonymous KS on KS.KeywordSynonymousID = K.keywordId
73 where K.Description = @search
74
75 union all
76
77 select S.SynKeywordID, KS.KeywordSynonymousID
78 from Synonymous S
79 inner join KeywordSynonymousAll KS on KS.KeywordID = S.SynKeywordID
80 where KS.reversed = 0 -- to avoid infinite recursion
81
82 union all
83
84 select KS.KeywordSynonymousID, S.SynKeywordID
85 from Synonymous S
86 inner join KeywordSynonymousAll KS on KS.KeywordID = S.KeywordID
87 where KS.reversed = 1 -- to avoid infinite recursion
88
89)
90
91-- finally output the result
92select distinct K.Description
93 from Synonymous S
94 inner join Keywords K on K.KeywordID = S.keywordID
95
96ASP.NET MVC
97 MVC
98 MVC3
99
100-- finally output the result
101select distinct T.Description
102 from (
103 select K.Description
104 from Synonymous S
105 inner join Keywords K on K.KeywordID = S.keywordID
106
107 union
108
109 select Description
110 from Keywords
111 where Description = @search) T
112
113C#
114
115HTML
116
117CREATE TABLE [dbo].[Keywords]
118[KeywordID] [int] IDENTITY(1,1) NOT NULL,
119[Description] [varchar](200) NOT NULL
120
121select * from Keywords
122
123 1 MVC
124 2 HTML
125 3 C#
126 4 ASP.NET MVC
127 5 MVC3
128 6 C sharp
129
130CREATE TABLE [dbo].[KeywordSynonymity]
131 [SynonymityID] [int] NOT NULL,
132 [KeywordID] [int] NOT NULL
133
134select * from KeywordSynonymous
135
1361 1 --- for the 1 (MVC) and 5 (MVC3)
1371 5 --- being synonymous
1382 3 --- for the 3 (C#) and 6 (C sharp)
1392 6 --- being synonymous
140
141CREATE PROCEDURE [dbo].[AddKeyword]
142 @newKeyword [varchar](200),
143 @synonymKeyword [varchar](200) = NULL
144AS
145BEGIN
146 SET NOCOUNT ON;
147
148 set transaction isolation level serializable
149
150 begin transaction
151
152 if EXISTS (select 1 from Keywords where [Description] = @newKeyword)
153 begin
154 commit transaction
155 return
156 end
157
158 declare @masterKeywordId int
159
160 select
161 @masterKeywordId = ISNULL(KeywordSynonymous.KeywordID, Keywords.KeywordID)
162 from
163 Keywords
164 left join
165 KeywordSynonymous
166 on
167 Keywords.KeywordID = KeywordSynonymous.KeywordSynonymousID
168 where
169 [Description] = @synonymKeyword
170
171 insert into Keywords VALUES (@newKeyword)
172
173 if @masterKeywordId is not null
174 insert into KeywordSynonymous VALUES (@masterKeywordId,SCOPE_IDENTITY())
175
176 commit transaction
177
178END
179
180CREATE PROCEDURE [dbo].[GetSynonymKeywords]
181 @keyword [varchar](200)
182AS
183BEGIN
184 SET NOCOUNT ON;
185
186 declare @masterKeywordId int
187
188 select
189 @masterKeywordId = ISNULL(KeywordSynonymous.KeywordID, Keywords.KeywordID)
190 from
191 Keywords
192 left join
193 KeywordSynonymous
194 on
195 Keywords.KeywordID = KeywordSynonymous.KeywordSynonymousID
196 where
197 [Description] = @keyword
198
199 select
200 KeywordId,[Description]
201 from
202 Keywords
203 where
204 KeywordId = @masterKeywordId
205 union
206 select
207 Keywords.KeywordId,[Description]
208 from
209 KeywordSynonymous
210 join
211 Keywords
212 on
213 KeywordSynonymous.KeywordSynonymousID = Keywords.KeywordId
214 where
215 KeywordSynonymous.KeywordId = @masterKeywordId
216
217END
218
219EXEC [dbo].[AddKeyword] @newKeyword = N'MVC'
220EXEC [dbo].[AddKeyword] @newKeyword = N'ASP.NET MVC', @synonymKeyword = 'MVC'
221EXEC [dbo].[AddKeyword] @newKeyword = N'MVC3', @synonymKeyword = 'ASP.NET MVC'
222
223[dbo].[GetSynonymKeywords] @keyword = N'MVC3'
224[dbo].[GetSynonymKeywords] @keyword = N'ASP.NET MVC'
225[dbo].[GetSynonymKeywords] @keyword = N'MVC3'
226
227DECLARE @TempKeywordID TABLE (KeywordID int)
228INSERT INTO @TempKeywordID (KeywordID)(select KeywordID from Keywords where [Description] = @SearchKeyword)
229
230DECLARE @intFlag INT
231SET @intFlag = 1
232
233WHILE (@intFlag <=(Select Count(KeywordSynonymousID) from KeywordSynonymous)) --Loop for all records in KeywordSynonymous
234BEGIN
235 INSERT INTO @TempKeywordID (KeywordID)(Select KeywordSynonymousID from KeywordSynonymous where KeywordID in (Select KeywordID from @TempKeywordID))
236 INSERT INTO @TempKeywordID (KeywordID)(Select KeywordID from KeywordSynonymous where KeywordSynonymousID in (Select KeywordID from @TempKeywordID))
237
238 SET @intFlag = @intFlag + 1
239END
240
241SELECT * FROM Keywords WHERE KeywordID IN (SELECT * FROM @TempKeywordID)