October 11, 2010 at 6:27 am
Hi, I am assigning tags(keywords) to a test. Given a Test ID I want to find out other tests that have related tags, and sort the results such that tests which have the most number of common tags get sorted higher.
Table Structure is as follows:
TABLE_TEST
---------------------------
TEST_IDTEST_NAME
1History
2Geography
3Maths
TABLE_TEST_TAG
-------------------------------------------------
TEST_TAG_IDTEST_IDTAG_NAMETAG_ID
-------------------------------------------------
11A1
21B2
31C3
41D4
52A1
62B2
72P12
82Q13
92R14
103A1
113D4
123E5
133C3
TABLE_TAG
---------------------------
TAG_IDTAG_NAME
---------------------------
1A
2B
3C
4D
12P
13Q
14R
5E
So far I can only get the Tests that have a common tag with the Test whose ID=1. But I am not being able to figure out how to evaluate which one has the most number of common tags with Test ID = 1
SELECT *
FROM TABLE_TEST
WHERE TEST_ID IN
(
SELECT TAG_ID
FROM TABLE_TEST_TAG
WHERE TEST_ID = '1'
)
The correct order should be:
3Maths (this has 3 common tags)
2Geography (this has 2 common tags)
Will SQL Ranking help ?
October 11, 2010 at 7:44 am
You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply