Posted by Erland Sommarskog on 01/03/07 22:24

EvoEight@gmail.com (EvoEight@gmail.com) writes:
> 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?

Could be as easy as?

Select e.name, estore_product_id, count(name) hits
FROM crm_contact_publishers p
JOIN estore_products e ON c.product_id = p.product_id
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

There is a good recommendation for posting queries like this and
that is that you post:

o CREATE TABLE statements for your tables, preferably with
PRIMARY and FOREIGN KEY definitions.
o INSERT statements with sample data.
o The desired output with sample data.

That makes it possible to develop a tested solution. The less information
you post, the wilder the guesses you will be you get in response.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]