Query for search engine

On Fri, 21 Jan 2005 11:51:02 -0800, Ray wrote:

>Sample data:
>iParseID iPageID sPhrase iR
ank
>22592 daacf052-122a-446b-99c4-ead865dd468f home 33
>22593 2cb2ebf2-4268-498a-9128-d223f09253e7 page 43
>22594 2cb2ebf2-4268-498a-9128-d223f09253e7 home 15
>22595 588e6a38-5f0e-4704-87f5-e5d17f6033f0 trade 24
>22596 e51b431c-4b29-4316-8f45-0ac24b8fc812 home 23
>22597 daacf052-122a-446b-99c4-ead865dd468f london 51
>When I do a search for "london home" then I only want the iPageID of record
>22597 returned and not for 22594 nor 22596.
>Both records 22594 and 22596 has "home" as a value for sParse, but there ar
e
>no other records where the iPageID is the same as for records 22594 and 225
96
>and has "london" as the value for sPhrase.
>I hope this makes sence.
Hi Ray,
I think it does. The only thing I don't know what rank to use for the
ordering, since the iPageID of rows 22597 and 22592 is associated with
iRank 33 and iRank 23. My guess is that you want the lowest of the two.
Try if this code works for you:
SELECT a.iPageID
FROM tbParse AS a
INNER JOIN tbParse AS b
ON b.iPageID = a.iPageID
WHERE a.sPhrase LIKE '%searchword1%'
AND b.sPhrase LIKE '%searchword2%'
ORDER BY CASE WHEN a.iRank < b.iRank THEN a.iRank ELSE b.iRank END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)I take both your and Michael's comments onboard, but what if there are more
than two search phrases? Do I have to build the query string dynamically
according to the number of search phrases or is their an easier way?
I have not test either of the queries, as it is already 11:30pm, but will
give it a go in the morning and get back with the results.
Thanks in advance!
"Hugo Kornelis" wrote:

> On Fri, 21 Jan 2005 11:51:02 -0800, Ray wrote:
>
> Hi Ray,
> I think it does. The only thing I don't know what rank to use for the
> ordering, since the iPageID of rows 22597 and 22592 is associated with
> iRank 33 and iRank 23. My guess is that you want the lowest of the two.
> Try if this code works for you:
> SELECT a.iPageID
> FROM tbParse AS a
> INNER JOIN tbParse AS b
> ON b.iPageID = a.iPageID
> WHERE a.sPhrase LIKE '%searchword1%'
> AND b.sPhrase LIKE '%searchword2%'
> ORDER BY CASE WHEN a.iRank < b.iRank THEN a.iRank ELSE b.iRank END
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Fri, 21 Jan 2005 15:33:03 -0800, Ray wrote:

>I take both your and Michael's comments onboard, but what if there are more
>than two search phrases? Do I have to build the query string dynamically
>according to the number of search phrases or is their an easier way?
Hi Ray,
Thinking it over, there might be another way that is easier expanded.
SELECT iPageID, MIN(iRank) AS Rank
FROM tbParse
WHERE sPhrase LIKE '%searchword1%'
OR sPhrase LIKE '%searchword2%'
GROUP BY iPageID
HAVING COUNT(*) = 2
ORDER BY Rank
(untested)
If your number of search strings varies, you can put the search strings in
a table and change the query as follows:
SELECT tbParse.iPageID, MIN(tbParse.iRank) AS Rank
FROM tbParse
INNER JOIN SearchWords
ON tbParse.sPhrase LIKE '%' + SearchWords.Word + '%'
GROUP BY tbParse.iPageID
HAVING COUNT(*) = (SELECT COUNT(*) FROM SearchWords)
ORDER BY Rank
(untested)
I also second Michael's thoughts: if sPhrase is indexed, consider using
sPhrase LIKE 'searchword%' (only a wildcard % at the end)
or even
sPhrase = 'searchword' (test for equality only).
unless you enjoy prolonged waits while SQL Server is busy...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Brilliant! Just what I was looking for.
Thanks for all your replies. The help is much appreciated.
Ray
"Hugo Kornelis" wrote:

> On Fri, 21 Jan 2005 15:33:03 -0800, Ray wrote:
>
> Hi Ray,
> Thinking it over, there might be another way that is easier expanded.
> SELECT iPageID, MIN(iRank) AS Rank
> FROM tbParse
> WHERE sPhrase LIKE '%searchword1%'
> OR sPhrase LIKE '%searchword2%'
> GROUP BY iPageID
> HAVING COUNT(*) = 2
> ORDER BY Rank
> (untested)
> If your number of search strings varies, you can put the search strings in
> a table and change the query as follows:
> SELECT tbParse.iPageID, MIN(tbParse.iRank) AS Rank
> FROM tbParse
> INNER JOIN SearchWords
> ON tbParse.sPhrase LIKE '%' + SearchWords.Word + '%'
> GROUP BY tbParse.iPageID
> HAVING COUNT(*) = (SELECT COUNT(*) FROM SearchWords)
> ORDER BY Rank
> (untested)
> I also second Michael's thoughts: if sPhrase is indexed, consider using
> sPhrase LIKE 'searchword%' (only a wildcard % at the end)
> or even
> sPhrase = 'searchword' (test for equality only).
> unless you enjoy prolonged waits while SQL Server is busy...
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>