Posted by EvoEight@gmail.com on 01/03/07 19:59

I changed the data type and now I have the correct columns being
searched but now I have a new problem. When I search for a program
name, it returns just fine (the first part of the union) but when I
search for a persons name (the latter half of the union) I need it to
return the program's name from estore_products not the name from
crm_contact_publishers. Any ideas on how to get it to output the name
column from estore_products? The product IDs are the same on each
table. TIA
Ryan


My code:

Declare @Keywords varchar(2000)
Select @Keywords = 'zwier' -- Static for now and we'll pass it off
later from vb
Select name, product_id, count(name) hits
FROM estore_products
INNER JOIN sequence
ON estore_products.name like '%' +
Substring(' ' + @keywords + ' ',seq,
CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq)
+ '%'
WHERE
seq <= len(' ' + @keywords + ' ') and
Substring(' ' + @keywords + ' ', seq - 1, 1) = ' ' and
CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq > 0
Group by product_id, name, description
UNION
Select name, estore_product_id, count(name) hits
FROM crm_contact_publishers
INNER JOIN sequence
ON crm_contact_publishers.name like '%' +
Substring(' ' + @keywords + ' ',seq,
CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq)
+ '%'
WHERE
seq <= len(' ' + @keywords + ' ') and
Substring(' ' + @keywords + ' ', seq - 1, 1) = ' ' and
CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq > 0
Group by name, estore_product_id
ORDER BY Hits DESC

[Back to original message]