Using DISTINCT With CONTAINSTABLE
-
I have a *big* query that searches for articles on a knowledgebase. The article text is stored in a text field which has been Full-Text indexed, and then there are a bunch of INNER JOINS that narrow down the search (by product, version, category, etc.). Still, when I get the results back, I have a bunch of duplicates.
I can't seem to use the familiar DISTINCT keyword in order to remove the dupes, becuase DISTINCT doesn't play nicely with Text fields.
What kind of options do I have to eliminate the dupes?
Here is a "plain vanilla" version of the query to give you an idea of what I am doing. It is still missing all the "AND" statements that narrow down the products and versions, but you'll get the idea.
SELECT KEY_TBL.RANK, FT_TBL.Article_ID, FT_TBL.Article_Title, FT_TBL.Article_Content, FT_TBL.Article_Author, FT_TBL.Article_Type, FT_TBL.Date_Add,
FT_TBL.Date_Modified, FT_TBL.Article_Editor, FT_TBL.Article_Visible, FT_TBL.Article_Public, FT_TBL.Date_Expires, FT_TBL.PortalID,
Versions.Version_ID, Versions.Version_Name, CodeCountry.Code, CodeCountry.Description, Categories.Category_Name, Categories.Category_ID,
Products.Product_Name, Products.Product_ID, Users.UserID, Users.Email, Users.FirstName + ' ' + Users.LastName AS Author,
Users_1.FirstName + ' ' + Users_1.LastName AS Editor, Roles.RoleID, Roles.RoleName, ArticleTypes.Type_Name, ArticleTypes.Type_ID,
Users_1.UserID AS Editor_ID, Users_1.Email AS EditorEmail
FROM Articles FT_TBL INNER JOIN
CONTAINSTABLE(Articles, *, '(mysearchtext)') KEY_TBL ON FT_TBL.Article_ID = KEY_TBL. INNER JOIN
ArticleCategories ON FT_TBL.Article_ID = ArticleCategories.Article_ID INNER JOIN
ArticleCountries ON FT_TBL.Article_ID = ArticleCountries.Article_ID INNER JOIN
ArticleProducts ON FT_TBL.Article_ID = ArticleProducts.Article_ID INNER JOIN
ArticleRoles ON FT_TBL.Article_ID = ArticleRoles.Article_ID INNER JOIN
ArticleTypes ON FT_TBL.Article_Type = ArticleTypes.Type_ID INNER JOIN
ArticleVersions ON FT_TBL.Article_ID = ArticleVersions.Article_ID INNER JOIN
Categories ON ArticleCategories.Category_ID = Categories.Category_ID INNER JOIN
CodeCountry ON ArticleCountries.Country_Code = CodeCountry.Code INNER JOIN
Products ON ArticleProducts.Product_ID = Products.Product_ID AND Categories.Product_ID = Products.Product_ID INNER JOIN
Roles ON ArticleRoles.RoleID = Roles.RoleID INNER JOIN
UserRoles ON Roles.RoleID = UserRoles.RoleID INNER JOIN
Users ON FT_TBL.Article_Author = Users.UserID INNER JOIN
Users Users_1 ON FT_TBL.Article_Editor = Users_1.UserID INNER JOIN
Versions ON ArticleVersions.Version_ID = Versions.Version_ID AND Products.Product_ID = Versions.Product_ID
WHERE (FT_TBL.PortalID = 0) AND (FT_TBL.Date_Expires IS NULL OR
FT_TBL.Date_Expires > GETDATE()) AND (FT_TBL.Article_Visible = 1)
ORDER BY KEY_TBL.RANK DESC
-
Sorry I don't know a good answer (all i would know to do is turn it into something that could be grouped/distinct by), but i do have a curiosity question.
Why do you want to eliminate duplicates from a text search? Don't they apply to different articles?
-
Nah - since each article can be related to multiple products, versions, etc., it return the same article number over and over again.
I guess I'll have to do something like remove the actual text of the article from the search so I can run DISTINCT on it, and then make that a sub SELECT statement in another SELECT statement that pulls the articles themselves. It is just a very complicated statement as it stands...
-
Ah ok i understand now. Yeah the article unique id would be the only way i'd know to do it. Not that there isnt another one... i constantly see things i had no clue about posted on these forums.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply