Steps to create related posts query in SQL.
Index, catalog, matching posts for certain words or tags
These were working by the time of documenting
Edit: 2023-04-26: Things changed, please see new notes below
Edit: 2023-04-26
Things changed
CREATE TABLE Flags (Country nvarchar(30) NOT NULL, FlagColors varchar(200));
CREATE UNIQUE CLUSTERED INDEX FlagKey ON Flags(Country);
INSERT Flags VALUES ('France', 'Blue and White and Red');
INSERT Flags VALUES ('Italy', 'Green and White and Red');
INSERT Flags VALUES ('Tanzania', 'Green and Yellow and Black and Yellow and Blue');
SELECT * FROM Flags;
GO
CREATE FULLTEXT CATALOG TestFTCat;
CREATE FULLTEXT INDEX ON Flags(FlagColors) KEY INDEX FlagKey ON TestFTCat;
GO
SELECT * FROM Flags;
SELECT * FROM FREETEXTTABLE (Flags, FlagColors, 'Blue');
SELECT * FROM FREETEXTTABLE (Flags, FlagColors, 'Yellow');
From
https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/freetexttable-transact-sql?view=sql-server-ver16
Related Posts
> Run below SQL lines
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
To create Text index, go to Design table > Full-text indexis > Add
SELECT * FROM PoetryPart AS Tab1 INNER JOIN CONTAINSTABLE(PoetryPart, Body, 'سيدة') AS KEY_TBL
ON Tab1.ID = KEY_TBL.[KEY]
order by Key_TBL.Rank desc
http://msdn.microsoft.com/en-us/library/ms189760.aspx
===========================================
> Match related posts
match from table
sql="SELECT * FROM Lessons WHERE MATCH (title,description,tags) AGAINST ('" & ATags & "') and status=5 limit 5;"
===========================================
>Order by ntext
Select * from Parts inner join Book_Part on Book_Part.PartID=Parts.ID order by cast(Parts.Title as varchar(500)) asc cast(Parts.Title as varchar(500)) asc
Things changed
CREATE TABLE Flags (Country nvarchar(30) NOT NULL, FlagColors varchar(200));
CREATE UNIQUE CLUSTERED INDEX FlagKey ON Flags(Country);
INSERT Flags VALUES ('France', 'Blue and White and Red');
INSERT Flags VALUES ('Italy', 'Green and White and Red');
INSERT Flags VALUES ('Tanzania', 'Green and Yellow and Black and Yellow and Blue');
SELECT * FROM Flags;
GO
CREATE FULLTEXT CATALOG TestFTCat;
CREATE FULLTEXT INDEX ON Flags(FlagColors) KEY INDEX FlagKey ON TestFTCat;
GO
SELECT * FROM Flags;
SELECT * FROM FREETEXTTABLE (Flags, FlagColors, 'Blue');
SELECT * FROM FREETEXTTABLE (Flags, FlagColors, 'Yellow');
From
https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/freetexttable-transact-sql?view=sql-server-ver16
Related Posts
> Run below SQL lines
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
To create Text index, go to Design table > Full-text indexis > Add
SELECT * FROM PoetryPart AS Tab1 INNER JOIN CONTAINSTABLE(PoetryPart, Body, 'سيدة') AS KEY_TBL
ON Tab1.ID = KEY_TBL.[KEY]
order by Key_TBL.Rank desc
http://msdn.microsoft.com/en-us/library/ms189760.aspx
===========================================
> Match related posts
match from table
sql="SELECT * FROM Lessons WHERE MATCH (title,description,tags) AGAINST ('" & ATags & "') and status=5 limit 5;"
===========================================
>Order by ntext
Select * from Parts inner join Book_Part on Book_Part.PartID=Parts.ID order by cast(Parts.Title as varchar(500)) asc cast(Parts.Title as varchar(500)) asc
Views 104
Downloads 38
CodeID
DB ID