Complicated search query

[Puiu]

I have a database with 2 tables: Images and Keywords.

In the Keywords table i have the following data:

 

IdKeyword_____IdImage_________Keyword

------------------------------------------------

1____________10__________forest wood elf green

2____________11__________city steel car yellow subway

3____________12__________bird computer blue sky

 

On my website the user enters in a textbox these words: forest city bird

The select query would be something like this:

 

Select i.* from Images i, keywords k where i.idimage=k.idimage and k.keyword like 'forest city bird'

 

This query doesn't return anything. I know why, but I�d like to know how I can make this work.

So far I�ve used full text search, but this works only when mssql server is installed. If you have msde you can�t use full-text search.

If you have a solution that involves vb code or asp.net code, that�s fine too.

Any suggestions?

[Nerseus]

If I interpret your sample data right, you have keywords all grouped together, so that one row of data has the value "forest wood elf green"? That seems like a bad design.

 

But you can still make your query work, it just won't be as effecient or as accurate. You'll need to parse the text entered by the user into separate strings and then build multiple LIKE statements.

 

So if they enter 'forest city bird', you have to break that into 'forest', 'city' and 'bird'. Here's some sample code:

string fullSearchString = "forest city bird";
string[] searchStrings = fullSearchString.Split(" ");

 

You'll probably want something more robust than "Split", to remove two spaces and etc. - I leave that up to you.

 

Now build your WHERE clause to look like this:

...WHERE i.idimage=k.idimage AND (k.keyword like '%forest%' and k.keyword LIKE '%city%' AND k.keyword LIKE '%bird%')

 

That would use an 'AND' join - all keywords must be present in a single file. If you want an 'OR' join, change the ANDs to ORs inside the parens above.

 

The ineffeciency comes from doing a LIKE with a wildcard on the front, as in '%forest%'. It's much more efficient to just put one on the end, as in 'forest%'. Or, if you require exact matching, then put the keywords in the table one at a time. If that's possible, just ask and we can help.

 

-ner

[kejpa]

I have a database with 2 tables: Images and Keywords.

In the Keywords table i have the following data:

 

IdKeyword_____IdImage_________Keyword

------------------------------------------------

1____________10__________forest wood elf green

2____________11__________city steel car yellow subway

3____________12__________bird computer blue sky

 

From my point of veiw IdKeyword looks like an Identity field , right? Not too sure you need it, IdImage and Keyword would make a good primary key.

The structure you have is right just keep one keyword in each record.

IdImage______Keyword

------------------------

10__________forest

10__________wood

10__________elf

11__________city

11__________steel

11__________car

11__________yellow

11__________subway

12__________bird

12__________computer

12__________blue

12__________sky

Which will be able to give you a query like...

Select i.* from Images i 
inner join Keywords k on k.IdImage=i.IdImage 
where k.Keyword in ('city', 'bird', 'forest')

[Puiu]

Thank you people, I�m almost done with this..but I have a small problem:

 

I have a stored procedure named Search :

 

Create Proc SP_Search

@WClause varchar(1000)

 

as

exec ('select distinct m.idmedia, m.idcampaign, m.[description] from Media m, keywords k where m.idmedia=k.idmedia AND (''' + @WClause + ''') ')

 

 

It�s the same like the first one, only instead of images I have Media�

However, when I try to call SP_Search like this :

 

SP_Search �m.[description] like bird%�

 

I get an error. I can�t figure out how many quotes I must use. In an earlier post Nerseus explained me something similar, I know, but here is different because the parameter must be something like: m.[description] like bird%

How should i pass corectly that parameter!?

 

As for the Keywords table it didn�t cross my mind to use the idimage and the keyword as the primary key, it would have been a good idea.

I put all the keywords on the same row because I thought I�d save some disk space.. what do you think ?

Thank you!

[kejpa]

Disk space is cheap. Wildcard table scans are expensive.

I'd go for one keyword per record, the only overhead storage is the imageId.

 

Your where clause should be m.[description] like 'bird%'

You're missing the quotes around the string to search for.

 

Another thing...

Avoid using sp_ as prefix to your stored procedures. If you're unlucky enough M$ comes up with a system stored procedure with the same name and yours will never run, not even when prefixing it with your database name

[Puiu]

Thank you for the advice Kejpa!

If i use SP_Search m.[description] like 'bird%' i get the following error: Line 1: Incorrect syntax near '.'.

 

If i use SP_Search 'm.[description] like 'bird%' ' i get the error:

Line 1: Incorrect syntax near 'bird'.

 

I know i'm close :(

[Nerseus]

Try:

SP_Search 'm.[description] like ''bird%'' '

 

You need to double up the single quotes inside of quotes. To test future strings, use SELECT on your string to show what it is. For example:

Create Proc SP_Search
@WClause varchar(1000)

as

DECLARE @sql varchar(1000)

SET @sql = 'select distinct m.idmedia, m.idcampaign, m.[description] from Media m, keywords k where m.idmedia=k.idmedia AND (''' + @WClause + ''') '

SELECT @sql
-- exec (@sql)

 

I commented out the exec line so you can test the string.

 

I suspect you're still going to have a problem since your "exec" line is wrapping @WClause with single quotes.

 

I can't say this loud enough, but [highlight]you really, REALLY should be using parameters for dynamic SQL in SQL Server[/highlight]. Look up the proc sp_executesql for a start. It will give samples on how to do dynamic SQL properly.

 

It may be a bit of a learning curve, but not more than an hour or two to get it working. That's certainly faster than debugging a custom dynamic SQL solution, which is also more likely to be buggy.

 

-ner

[Puiu]

I've got it!!! :)

-----------------------------------

create proc SP_Search

@WClause varchar(1000)

 

as

declare @SQL varchar(1000)

 

set @SQL='select distinct m.idmedia, m.idcampaign, m.[description] from Media m, keywords k where m.idmedia=k.idmedia AND ('

 

set @SQL =@SQL + @WClause + ')'

 

--Select @SQL

exec (@SQL)

-------------------------

 

Sorry Nerseus, i didn't quite understand what you ment by you "really, REALLY should be using parameters for dynamic SQL in SQL Server.", i thought that @WClause was my parameter, but you probably ment what i did now...anyway thank you for the answer again! :)

I'm happy now!

[PlausiblyDamp]

sp_executesql. You really should avoid concatenating strings to generate SQL, regardless of where it is done you introduce the potential for exploits.