SQL Algorithm Trouble - for loop to add and filter results

Question

I am struggling yet again with SQL with my search function.

Basically I want a stored proc that splits up a string into words then for each word I want to check using Full Text Search if it is in any of 2 tables.

If results are found in these tables it should add them to any results it found for the other words and return a set where the record is in both these sets.

So here is my algorithm

if null return all restaurants

declare results = empty

for each word
if in restaurant.name or cuisine.name
 addRowsToResults 
else if in restaurant.city
 addRowsToResults 
else if in restaurant.postcode
 addRowsToResults 


addRowsToResults 
results = (results + new results) where a match is in both

I have no idea where to start with this and I have searched google for ages but being a begginer in SQL I may be missing some terms.

Also is this even the best way to do this sort of thing?

Appreciate any help.

Edit: Just to give more info. City, Postcode and Name are all nvarchar fields in a Restaurants table. Cuisine name is in a different table and is linked through another table to restaurants as a restaurant can serve many types of cuisine.

All these fields have full text search indexing.

Answer 1

That isn't really the way SQL works. You can get some of that same functionality, but it won't look anything like what you are asking; you are writing (naturally enough) in a more procedural style.

For one thing "add results" is trying to bring (presumably) disparate things together into a single collection. You are - I think - asking for restaurants and cuisines and cities and postcodes all to go into the same collection, and the only kind of collection SQL has (roughly speaking) is tables, and within a table, all rows have the same type.

But maybe restaurants and cuisine and city and postcode are all fields of the same table? Well, then, for a given word you could say

SELECT * 
FROM   your_table
WHERE  restaurant like "%" + word + "%"
OR     cuisine    like "%" + word + "%"
OR     city       like "%" + word + "%"
OR     postcode   like "%" + word + "%";

It starts to get complicated when you want to match multiple words; this answer is intended as a starting point; maybe once you're more familiar with SQL you can ask easier-to-answer questions. Good luck!

Update based on your table descriptions

SELECT r.*
FROM       restaurant r
INNER JOIN link       k ON k.restaurant_id = r.restaurant_id
INNER JOIN cuisine    c on c.cuisine_id    = k.cuisine_id
WHERE  r.restaurant like "%" + word + "%"
OR     c.name       like "%" + word + "%"
OR     r.city       like "%" + word + "%"
OR     r.postcode   like "%" + word + "%";

And don't worry about which records come back "first". Your database will be fast enough that that should not be a concern.

Answer 2

Even if I wanted to, I couldn't give you the full story on this - you haven't posted the table definitions or any sample data.

That said, I'll make some guesses. I think first of all that you need to use SQL in a set-based manner. Don't think in terms of processing row by row - process a set. Then combine the sets together.

Answer 3

There is a split to words function here. Which returns a table you can join into.

You should also investigate metaphone or soundex, but to support that you will need to pre-index your data in a separate table or pre-calc the methaphone or soundex codes for each word in a separate field and index that as well.

Answer 4

It looks like your attempting to take a procedural approach to a set-based declarative query language.

First of all, you can split a string into a resultset using a table-valued user defined function. Something like the following will do -

CREATE function [dbo].[csl_to_table] ( @list nvarchar(MAX) )
RETURNS @list_table TABLE ([id] nvarchar(20)) -- set this to your maximum size string
AS
BEGIN
    DECLARE     @index INT,
                @start_index INT,
                @id nvarchar(20)  -- set this to your maximum size string

    SELECT @index = 1 
    SELECT @start_index = 1
    WHILE @index <= DATALENGTH(@list)
    BEGIN

        IF SUBSTRING(@list,@index,1) = ','
        BEGIN

                SELECT @id = SUBSTRING(@list, @start_index, @index - @start_index) 
                INSERT @list_table ([id]) VALUES (@id)
                SELECT @start_index = @index + 1
        END
        SELECT @index  = @index + 1
    END
    SELECT @id = SUBSTRING(@list, @start_index, @index - @start_index )
    INSERT @list_table ([id]) VALUES (@id)
    RETURN
END

Then using your resultset, you can join to the tables that you wish to query for matches, and return a resultset of matches.

Answer 5

If you are looking for a For Each loop in SQL most SQL dialects have Cursors that allow you to select all of the records using some query and then iterate over the records by using a Cursor.

Answer 6

To use fulltext search you want to use the CONTAINS keyword. Look up how to use it in Books online (I have to get to a meeting or I'd provide an example).

Answer 7

Here is a split function that has an optional parameter of a delimiter...

CREATE FUNCTION [dbo].[fnSplit]( @List VARCHAR(4000), @Delimiter CHAR(1) = ',' )
    RETURNS @Result TABLE (item VARCHAR(100))
    BEGIN
     DECLARE @Item VARCHAR(100)
     WHILE CHARINDEX(@Delimiter,@List,0) <> 0
     BEGIN
      SELECT @Item = SUBSTRING(@List,1,CHARINDEX(@Delimiter,@List,0)-1)
       , @List = SUBSTRING(@List,CHARINDEX(@Delimiter,@List,0)+1,LEN(@List))

      IF LEN(@Item) > 0
       INSERT INTO @Result
        SELECT @Item
     END
     IF LEN(@List) > 0
      INSERT INTO @Result
       SELECT @List
     RETURN
    END
GO

Then your query could look something like this...

SELECT DISTINCT
      'restaurants' AS [source]
    , r.ID AS [ID] --Assuming ID is your primary key column
    , r.name + '(' + r.city + ', ' + r.state + ')' AS [Description]
FROM restaurants AS [r]
JOIN [dbo].[fnSplit](@Query,' ') AS [terms]
ON
    ISNULL(r.name,'')
    +','+ISNULL(r.city,'')
    +','+ISNULL(r.postcode,'') LIKE '%'+terms.item+'%'

UNION SELECT DISTINCT
      'cuisine' AS [source]
    , r.ID AS [ID] --Assuming ID is your primary key column
    , r.name AS [Description]
FROM cuisine AS [r]
JOIN [dbo].[fnSplit](@Query,' ') AS [terms]
ON
    ISNULL(r.name,'') LIKE '%'+terms.item+'%'

The fnSplit function breaks your query terms into rows in a table variable and returns it. The select queries then joins against the resultant table. The query is distinct so that only 1 instance of a row is returned regardless of how many terms are matched in the query. The join condition could easily be broken into a series of AND/OR conditions but I think LIKE operations are more expensive than concatenation so I'm just concatenating the columns.

UPDATE

The following simplified query can be used since Full-Text Indexing is enabled.

DECLARE @Phrase VARCHAR(4000)
SELECT @Phrase = item + '" OR "' FROM [dbo].[fnSplit](@Query,' ')
SET @Phrase = SUBSTRING(@Phrase,0,LEN(@Phrase)-6)


SELECT DISTINCT
      'restaurants' AS [source]
    , r.ID AS [ID] --Assuming ID is your primary key column
    , r.name + '(' + r.city + ', ' + r.state + ')' AS [Description]
FROM restaurants AS [r]
WHERE
    CONTAINS(r.name,@Phrase)
    OR CONTAINS(r.city,@Phrase)
    OR CONTAINS(r.postcode,@Phrase)

UNION SELECT DISTINCT
      'cuisine' AS [source]
    , r.ID AS [ID] --Assuming ID is your primary key column
    , r.name AS [Description]
FROM cuisine AS [r]
WHERE CONTAINS(r.name,@Phrase)