I have a SQL 2008 Express database, which have following tables:
CREATE TABLE Videos
(VideoID bigint not null,
Title varchar(100) NULL,
Description varchar(MAX) NULL,
isActive bit NULL )
CREATE TABLE Tags
(TagID bigint not null,
Tag varchar(100) NULL )
CREATE TABLE VideoTags
(VideoID bigint not null,
TagID bigint not null )
Now I need SQL query to search for word (i.e. Beyonce Halo Music Video) against these tables.
Which videos have:
For Title exact phrase will get 0.5 points
For Description exact phrase will get 0.4 points
For tags exact phrase will get 0.3 points
For title all words will get 0.2 points
For description all words will get 0.2 points
For title one or more words will get 0.1 points
For description one or more words will get 0.1 points
And I will show these videos on basis of points. What will be the SQL Query for this? A LINQ query will be more better.
If you know a better way to achieve this, please help.
