linq - Whats the best SQL Query to get Related Items? -

I have a small site where I want to get related video based on tags ... best MS SQL To get related videos based on tags 2005 queries you can give LINQ queries that will be awsome

Here is the database schema:.

  Create tab video (VIDEOID BigInt is not empty, title varchar (100) zero, tag varchar (max) zero, isActive bit zero) Insert video value (1, 'Bayonas Shakira - beautiful false Enter the video value (2, 'Beyonce ego remix', 'Bayonas, music, video', 1) in the video value (3, 'Beyonce ego', '', 'Shakira, Bayonas, music, video' Beyonce, Music, Video ', 1)  

What I want to do is watch the video with ID 1 on its tag Related videos should be shown on the basis and the most matching posts should be on top.

In advance thank you

Schema will tell you, denormalized with all tags for each video The tag is filled in string, badly designed for its purposes - there is no proper way to calculate a meaningful calculation in TSQL "equality between two stars in equality" and therefore there is no logical pair of objects relatively similarity There is an e-way to check and The car can be deemed "related" if the schema is untouchable, you have to implement a user-defined function (in C # or other .NET language) for this purpose, and you still have to scan the whole table because someone The appropriate way is not to index on this basis.

If you can redesign the schema (with two more tables: to hold the tag and to give many relationships between the tags and the video) there may be better possibilities; In this case, there are some indications that you expect a lot of (video of magnitude) video, how many different tags are overall, and roughly how many tags will be expected of a video, designing and effective methods You may be allowed to pursue your purposes.

EDIT: Per comment, apparently the schema can be redesigned, although still I did not give any indication about the numbers, so the proper index and the total secret will remain. . Anyway, it seems like there is something like schema (each table can be desired as other columns, just add them to the inquiry, and VARCHAR lenghts do not even talk):

  table Creating Video (VideoID INT Primary Key, Videotical VARCHAR (80)); Create a Table Tag (Tagged INT Primary Key, Text Text VARCHAR (20)); Create tablet videotag (video ID foreign key reference video, tagged foreign key reference tag, primary key (video ID, tag id));  

i.e. Just classic 'many-many relationships' textbook examples

Now the title of a video is given, @Mitile says, 5 videos can be easily asked by the title "Most related", for example: ) V2. Join VideoTitle, CommonTags DESC by CommonTags by VideoTitle, V2.VideoId from Video V2 (COUNT) * VideosTags on VT2 (V2.VideoID = VT2.VideoID) on MyTags (VT2.TagId = MyTags.TagId) group;