SQL Related posts

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

CodeFunctionName
What is this?

Public

Tested

Original Work
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

Views 73

Downloads 18

CodeID
DB ID

ANmarAmdeen
593
Attachments
Revisions

v2.0

Wednesday
April
26
2023