I am writing a query that would pull out results from a database using a fulltext search. The query goes like this
“select * from tbl_products WHERE MATCH(productname, productkeywords, product_description, productcategory) AGAINST (’“Dragon shoes”’)”
The query executes well and I get relevant results. The problem is there are some duplicate products in the table and I dont want to delete them because I make use of them for comparison purposes. However I do not want duplicate entities to show up when searching. Using the query mentioned above I get duplicate products too. In order to get rid of the duplicates I modified the search query as follows:
“select * from tbl_products WHERE MATCH(productname, productkeywords, product_description, productcategory) AGAINST (’“Dragon shoes”’) GROUP BY productname”
Here is the problem when I execute the 2nd query I get unique products but the search is not at all relevant. The least irrelevant result shows up first. But when I execute the 1st query the results are relevant but I see duplicate products.
IS THERE A WAY FOR ME TO USE GROUP BY OR SOME OTHER FUNCTION THAT WOULD PULL UP THE RESULTS WITH NO DUPLICATE ENTITIES AND NOT DESTROYING THE RELEVANCE OF SEARCH RESULTS…