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