Searching and Ranking by full/partial match.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Searching and Ranking by full/partial match.

Author

Topic

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-06-28 : 12:16:11

1. DDL, INSERTS + EXPECTED Results at top.
Explaination of problem at the bottom.

--DDL
CREATE TABLE [dbo].[AB_Item1]
([ID] [int] IDENTITY (1, 1) NOT NULL, Name char(40))
ON [PRIMARY]

CREATE TABLE [dbo].[AB_Item_Key_Word1]
([FKItemId] [int] NOT NULL ,[FKKeyWordId] [int] NOT NULL)
ON [PRIMARY]

ALTER TABLE [dbo].[AB_Item1]
WITH NOCHECK
ADD CONSTRAINT [PK_AB_Item1] PRIMARY KEY CLUSTERED
([ID])
ON [PRIMARY]

ALTER TABLE [dbo].[AB_Item_Key_Word1]
WITH NOCHECK
ADD CONSTRAINT [PK_AB_Item_Key_Word1] PRIMARY KEY CLUSTERED
([FKItemId],[FKKeyWordId])
ON [PRIMARY]

--Inserts
insert into dbo.ab_item1 (name) values ('andrew')
insert into dbo.ab_item1 (name) values ('paul')
insert into dbo.ab_item1 (name) values ('john')
insert into dbo.ab_item1 (name) values ('mark')
--
select * from dbo.ab_item1

insert into dbo.ab_item_key_word1 (FKItemId, FKKeyWordId) values (1,7)
insert into dbo.ab_item_key_word1 (FKItemId, FKKeyWordId) values (1,8)
insert into dbo.ab_item_key_word1 (FKItemId, FKKeyWordId) values (1,10)
insert into dbo.ab_item_key_word1 (FKItemId, FKKeyWordId) values (1,11)
insert into dbo.ab_item_key_word1 (FKItemId, FKKeyWordId) values (2,4)
insert into dbo.ab_item_key_word1 (FKItemId, FKKeyWordId) values (2,7)
insert into dbo.ab_item_key_word1 (FKItemId, FKKeyWordId) values (3,11)
insert into dbo.ab_item_key_word1 (FKItemId, FKKeyWordId) values (4,25)

--Select
select * from dbo.ab_item_key_word1

--expected output
--a) search with 7,8,11....ie list 1st those who match full search criteria,
--and then follow with those who match partial search criteria,
--in descending order of how many from the search options they match.
Andrew (3 matches) (7,8,11)
Paul (1 match) (7)
John (1 match) (11)

--6) search with 11....ie list 1st those who match full search criteria,
--and then follow with those who match partial search criteria,
--in descending order of how many from the search options they match.
Andrew (1 match) (11)
John (1 match) (11)

2. Problem.
I'm looking to find all ab_item1's ranked by the number of matches in their child table "AB_Item_Key_Word1, based on the search criteria keyed in (a CSV list).
I'm currently using nr's function "fn_PARSECSVSTRING" in my query - Performance not critical, but a better overall solution is needed....the parsename function is not the problem. It's how do I do this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources