Sql query Join problem

Hello I'm looking for the most efficient way to perform this query. I have a list of categories. (table name=Categories)  each category has a categoryname. this is linked to my businesses table(table name=Businesses) by a thrid table called "Category_Business".

In my Categories table I have a field called EnglishID Which contains the unique id (of type int) of the category name. And a field called LanguageID (of type char(3)) to specify which language to return. (language ID and englishID make up a unique field). There are currently 10,000 records in this table and it is expected to grow to over one million.

In my Businesses Table I have a field called txtBusinessID which is a unique identifier for the business. (of type char(7))

Category_Business looks like this.

EngID                      int      Unchecked
BusinessID      char(7)      Unchecked

Here is what I want to do.

I would like to be able to write a stored proc to return me a recordset of buiness Information given a search string.

Ex. Let's I have the catgories Auto, Automotive-Repair, Automotive-Sales, Automotive Parts, Beauty (each with their respecitive Engish Id's)

I want to create a point like system for search results.
If whole word match award 5 points,
If match contains word - 4 points
If match contains 75% of Search string - 3 points
If match contains 50% of search string - 2 points
If match contains 25% of search string - 1 points

If match contains less than 25% of search string - 0 points (Don't return)

When I search for Auto I want to get returned :
Auto (5 points)
Automotive-Repair (4 points),
Automotive-Sales (4 points),
Automotive Parts (4 points),
Beauty (0 points)

Then I can get the result back order by points  (search relevence). Then I can also order it by distance (which is another question which I will ask after this one).

Right now I have a wrote a stored proc to return everything for a given business. which is useless since no one knows the unique identifier but me. ( I did it more for layout testing). But now the time has come to have a real search.  This is what I have so far....


Might be of use...
You will notice at the end I try to do an order by distance. It doesn't work. I'm not familiar with inner, outer, left outer joins. but I've been told they make queries faster, so any pointers would be great.

Thanks in advance
Start Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:

ALTER PROCEDURE [dbo].[usp_ReturnSearchResults]
	-- Add the parameters for the stored procedure here
	@SearchText		nvarchar(max),
	@Username		nvarchar(50),
	@LangID			char(3),
	@UserPostalCode	char(7)
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
	DECLARE @userLat decimal(16,12);
	DECLARE @userLong decimal(16,12);
 
	SELECT @userLat=LATITUDE, @userLong=LONGITUDE 
	FROM postalCodes 
	WHERE POSTAL_CODE = @UserPostalCode
 
	Select txtBusinessID, txtBusinessName, imageLogo, txtCity, Bus_Lang_Desc.ShortDes as [ShortDesc], dbo.DistanceAssistant(@userLat,@userLong,LATITUDE,LONGITUDE) AS [distance]
	FROM dbo.businesses, dbo.Bus_Lang_Desc, dbo.PostalCodes 
	WHERE Bus_Lang_Desc.LanguageID = @LangID 
		AND businesses.txtBusinessID = Bus_Lang_Desc.BusID
		AND businesses.txtBusinessID = @SearchText
		AND businesses.txtPostalCode = PostalCodes.POSTAL_CODE
	ORDER BY [distance]
END