I am working on a search/tag system. My original query I wrote was for when I was storing 'title', 'description' and a comma seperated 'tags' column in my article/video table. I have since realised the advantage of normalising my tags. I now have three table to deal with...tbl_Articles
article_id
title
description
content
tbl_tag_index
tag_id (surrogate primary id)
tag_type (equals 1 for tbl_Articles,2 for tbl_videos)
tag_word_id (see table bellow)
tag_target_id (article_id/video_id -depends on tag_type)
tbl_tag_word
tag_word_id
tag_word (finally the actual tag)
This query returns the tags... only problem is it returns them as different rows. I guess I would need the results to be grouped to the same row so that my search query may work\[code\]SELECT * FROM `tbl_articles` A JOIN `tag_index` I ON A.article_id = I.tag_target_id JOIN tag_word W ON I.tag_word_id = W.tag_word_id WHERE I.tag_type_id = 1\[/code\]Here is my old search query\[code\]SELECT *, ((CASE WHEN `description` LIKE '%hotel%' THEN 1 ELSE 0 END) + (CASE WHEN `description` LIKE '%london%' THEN 1 ELSE 0 END) + (CASE WHEN `description` LIKE '%lazy%' THEN 1 ELSE 0 END) + (CASE WHEN `description` LIKE '%dog%' THEN 1 ELSE 0 END) +(CASE WHEN `title` LIKE '%hotel%' THEN 1 ELSE 0 END) + (CASE WHEN `title` LIKE '%london%' THEN 1 ELSE 0 END) + (CASE WHEN `title` LIKE '%lazy%' THEN 1 ELSE 0 END) + (CASE WHEN `title` LIKE '%dog%' THEN 1 ELSE 0 END) +(CASE WHEN `tags` LIKE '%hotel%' THEN 1 ELSE 0 END) + (CASE WHEN `tags` LIKE '%london%' THEN 1 ELSE 0 END) + (CASE WHEN `tags` LIKE '%lazy%' THEN 1 ELSE 0 END) + (CASE WHEN `tags` LIKE '%dog%' THEN 1 ELSE 0 END)) AS relevanceFROM `tbl_Articles`WHERE `description` LIKE '%hotel%' OR `description` LIKE '%london%' OR `description` LIKE '%lazy%' OR `description` LIKE '%dog%' OR `title` LIKE '%hotel%' OR `title` LIKE '%london%' OR `title` LIKE '%lazy%' OR `title` LIKE '%dog%' OR `tags` LIKE '%hotel%' OR `tags` LIKE '%london%' OR `tags` LIKE '%lazy%' OR `tags` LIKE '%dog%'ORDER BY relevance DESCLIMIT 0 , 10;\[/code\]
