sql server (alternate): I'm writing a search for our site and I'm running into a few problems.
When I run the query like this, it runs just fine:

Declare @Keywords varchar(2000)
Select @Keywords = 'modern trial advocacy canada'
Select product_id, name, 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 estore_products.product_id, name
ORDER BY Hits DESC

But when I add another column (for example a column called description)
from the select statement I get this error:

The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.

Long story short, what do I need to do to select more columns for the
final output?

Thanks,
Ryan

Hi Ryan

Have you read http://www.sommarskog.se/dyn-search.html ?

John

[quoted text, click to view]

EvoEight@gmail.com (EvoEight@gmail.com) writes:
[quoted text, click to view]

What is the data type of that column? And how does the query with that
column look like?

You would only get this error if you are using the text/ntext/image
data type.

What version of SQL Server are you using? On SQL 2005, there is no
need to use text & co, as there are new data types (n)varchar(MAX)
and varbinary(MAX) which can fit just as much data as text & co,
but which does not have all restrictions of the old types.


--
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

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

EvoEight@gmail.com (EvoEight@gmail.com) writes:
[quoted text, click to view]

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