|
|
|
date: Fri, 29 Aug 2008 09:14:32 -0700 (PDT),
group: microsoft.public.sqlserver.fulltext
back
Need help with sqlserver search stored procedure.
HI,
I am writing a stored procedure for my search box control. I found
this sample code which sets the @WORD1 to 'TBB-12' and searches 1
table for the information. I set the value to TBB-12 and specified to
look in the dbo.product_id. It did find the product id = TBB-12 on the
first result row, however, it return a bunch other rows, which none of
the column fields contains the value 'TBB-12'. In theory, a search
should only return 1 row, correct? Why am I getting a bunch extra
useless rows? What is wrong with the sql query?
Thanks,
John
3 * dbo.WordCount('TBB-12', (actress1.fullname + ' '
+actress2.fullname + actress3.fullname + ' ' + actress4.fullname + '
'+ actress5.fullname + ' '+ actress6.fullname + ' '+ actress7.fullname
+ ' '+ actress8.fullname + ' '+ actress9.fullname + ' '+
actress10.fullname)) + dbo.WordCount('TBB-12', dbo.dvd.product_id)
SELECT dbo.dvd.dvdid,
COALESCE(actress1.fullname+',',actress2.fullname+',',actress3.fullname
+',',actress4.fullname+',',actress5.fullname+',',actress6.fullname
+',',actress7.fullname+',',actress8.fullname+',',actress9.fullname
+',',actress10.fullname+',','')
+ COALESCE(actress2.fullname+',',actress3.fullname
+',',actress4.fullname+',',actress5.fullname+',',actress6.fullname
+',',actress7.fullname+',',actress8.fullname+',',actress9.fullname
+',',actress10.fullname+',','')
+ COALESCE(actress3.fullname+',',actress4.fullname
+',',actress5.fullname+',',actress6.fullname+',',actress7.fullname
+',',actress8.fullname+',',actress9.fullname+',',actress10.fullname
+',','')
+ COALESCE(actress4.fullname+',',actress5.fullname
+',',actress6.fullname+',',actress7.fullname+',',actress8.fullname
+',',actress9.fullname+',',actress10.fullname+',','')
+ COALESCE(actress5.fullname+',',actress6.fullname
+',',actress7.fullname+',',actress8.fullname+',',actress9.fullname
+',',actress10.fullname+',','')
+ COALESCE(actress6.fullname+',',actress7.fullname
+',',actress8.fullname+',',actress9.fullname+',',actress10.fullname
+',','')
+ COALESCE(actress7.fullname+',',actress8.fullname
+',',actress9.fullname+',',actress10.fullname+',','')
+ COALESCE(actress8.fullname+',',actress9.fullname
+',',actress10.fullname+',','')
+ COALESCE(actress9.fullname+',',actress10.fullname+',','')
+ COALESCE(actress10.fullname+',',''),
dbo.dvd.title,
dbo.dvd.product_id,
dbo.dvd.num_discs,
dbo.dvd.description,
CAST(ROUND(dbo.price.price,2) AS DECIMAL(4,2)),
dbo.dvd.cover,
3 * dbo.WordCount('TBB-12', (actress1.fullname + ' '
+actress2.fullname + actress3.fullname + ' ' + actress4.fullname + '
'+ actress5.fullname + ' '+ actress6.fullname + ' '+ actress7.fullname
+ ' '+ actress8.fullname + ' '+ actress9.fullname + ' '+
actress10.fullname)) + dbo.WordCount('TBB-12', dbo.dvd.product_id)
AS rank
from dbo.dvd
LEFT JOIN dbo.actress actress1 ON actress1.actressid =
dbo.dvd.actressid1
LEFT JOIN dbo.actress actress2 ON actress2.actressid =
dbo.dvd.actressid2
LEFT JOIN dbo.actress actress3 ON actress3.actressid =
dbo.dvd.actressid3
LEFT JOIN dbo.actress actress4 ON actress4.actressid =
dbo.dvd.actressid4
LEFT JOIN dbo.actress actress5 ON actress5.actressid =
dbo.dvd.actressid5
LEFT JOIN dbo.actress actress6 ON actress6.actressid =
dbo.dvd.actressid6
LEFT JOIN dbo.actress actress7 ON actress7.actressid =
dbo.dvd.actressid7
LEFT JOIN dbo.actress actress8 ON actress8.actressid =
dbo.dvd.actressid8
LEFT JOIN dbo.actress actress9 ON actress9.actressid =
dbo.dvd.actressid9
LEFT JOIN dbo.actress actress10 ON actress10.actressid =
dbo.dvd.actressid10
inner join dbo.actress
on dbo.dvd.actressid1 = dbo.actress.actressid
inner join dbo.price
on dbo.price.quantity = dbo.dvd.num_discs and dbo.price.producttype =
'dvd'
order by rank desc
date: Fri, 29 Aug 2008 09:14:32 -0700 (PDT)
author: unknown
Re: Need help with sqlserver search stored procedure.
This does not appear to be a full-text query. It does not use a freetext or
contains predicate.
Is this the complete query? What does the query look like in profiler?
wrote in message
news:bcbdd265-4c51-4be9-a8ed-ffcfc8c490ba@f63g2000hsf.googlegroups.com...
> HI,
>
> I am writing a stored procedure for my search box control. I found
> this sample code which sets the @WORD1 to 'TBB-12' and searches 1
> table for the information. I set the value to TBB-12 and specified to
> look in the dbo.product_id. It did find the product id = TBB-12 on the
> first result row, however, it return a bunch other rows, which none of
> the column fields contains the value 'TBB-12'. In theory, a search
> should only return 1 row, correct? Why am I getting a bunch extra
> useless rows? What is wrong with the sql query?
>
> Thanks,
>
> John
>
> 3 * dbo.WordCount('TBB-12', (actress1.fullname + ' '
> +actress2.fullname + actress3.fullname + ' ' + actress4.fullname + '
> '+ actress5.fullname + ' '+ actress6.fullname + ' '+ actress7.fullname
> + ' '+ actress8.fullname + ' '+ actress9.fullname + ' '+
> actress10.fullname)) + dbo.WordCount('TBB-12', dbo.dvd.product_id)
>
> SELECT dbo.dvd.dvdid,
> COALESCE(actress1.fullname+',',actress2.fullname+',',actress3.fullname
> +',',actress4.fullname+',',actress5.fullname+',',actress6.fullname
> +',',actress7.fullname+',',actress8.fullname+',',actress9.fullname
> +',',actress10.fullname+',','')
> + COALESCE(actress2.fullname+',',actress3.fullname
> +',',actress4.fullname+',',actress5.fullname+',',actress6.fullname
> +',',actress7.fullname+',',actress8.fullname+',',actress9.fullname
> +',',actress10.fullname+',','')
> + COALESCE(actress3.fullname+',',actress4.fullname
> +',',actress5.fullname+',',actress6.fullname+',',actress7.fullname
> +',',actress8.fullname+',',actress9.fullname+',',actress10.fullname
> +',','')
> + COALESCE(actress4.fullname+',',actress5.fullname
> +',',actress6.fullname+',',actress7.fullname+',',actress8.fullname
> +',',actress9.fullname+',',actress10.fullname+',','')
> + COALESCE(actress5.fullname+',',actress6.fullname
> +',',actress7.fullname+',',actress8.fullname+',',actress9.fullname
> +',',actress10.fullname+',','')
> + COALESCE(actress6.fullname+',',actress7.fullname
> +',',actress8.fullname+',',actress9.fullname+',',actress10.fullname
> +',','')
> + COALESCE(actress7.fullname+',',actress8.fullname
> +',',actress9.fullname+',',actress10.fullname+',','')
> + COALESCE(actress8.fullname+',',actress9.fullname
> +',',actress10.fullname+',','')
> + COALESCE(actress9.fullname+',',actress10.fullname+',','')
> + COALESCE(actress10.fullname+',',''),
> dbo.dvd.title,
> dbo.dvd.product_id,
> dbo.dvd.num_discs,
> dbo.dvd.description,
> CAST(ROUND(dbo.price.price,2) AS DECIMAL(4,2)),
> dbo.dvd.cover,
> 3 * dbo.WordCount('TBB-12', (actress1.fullname + ' '
> +actress2.fullname + actress3.fullname + ' ' + actress4.fullname + '
> '+ actress5.fullname + ' '+ actress6.fullname + ' '+ actress7.fullname
> + ' '+ actress8.fullname + ' '+ actress9.fullname + ' '+
> actress10.fullname)) + dbo.WordCount('TBB-12', dbo.dvd.product_id)
> AS rank
> from dbo.dvd
> LEFT JOIN dbo.actress actress1 ON actress1.actressid =
> dbo.dvd.actressid1
> LEFT JOIN dbo.actress actress2 ON actress2.actressid =
> dbo.dvd.actressid2
> LEFT JOIN dbo.actress actress3 ON actress3.actressid =
> dbo.dvd.actressid3
> LEFT JOIN dbo.actress actress4 ON actress4.actressid =
> dbo.dvd.actressid4
> LEFT JOIN dbo.actress actress5 ON actress5.actressid =
> dbo.dvd.actressid5
> LEFT JOIN dbo.actress actress6 ON actress6.actressid =
> dbo.dvd.actressid6
> LEFT JOIN dbo.actress actress7 ON actress7.actressid =
> dbo.dvd.actressid7
> LEFT JOIN dbo.actress actress8 ON actress8.actressid =
> dbo.dvd.actressid8
> LEFT JOIN dbo.actress actress9 ON actress9.actressid =
> dbo.dvd.actressid9
> LEFT JOIN dbo.actress actress10 ON actress10.actressid =
> dbo.dvd.actressid10
> inner join dbo.actress
> on dbo.dvd.actressid1 = dbo.actress.actressid
> inner join dbo.price
> on dbo.price.quantity = dbo.dvd.num_discs and dbo.price.producttype =
> 'dvd'
> order by rank desc
date: Fri, 29 Aug 2008 20:44:14 -0400
author: Hilary Cotter
|
|
