Hi

I have the following SQL from Mysql and I would like to run it thru ASP+Access

strsql = "select t.* from forum_post p, forum_thread t where p.thread_id=t.thread_id and (0 or (p.post_message LIKE '%test%' or t.thread_subject Like '%test%')) GROUP BY t.tid Order by thread_date DESC"

When I run this query, I got "Cannot group on fields selected with '*' (t)."

How can I make it to work for Access MDB? thanks



Somebody suggested me :

Select forum_thread.* where forum_post.post_message like '%test%' or forum_thread.thread_subject like '%test%' from forum_post inner join forum_post on forum_thread.thread_id=forum_post.thread_id

But it doesn't work as well, error message for this is:
Syntax error in query expression 'forum_thread.* where forum_post.post_message like '%test%' or forum_thread.thread_subject like '%test%''.

try this...

Error:
Cannot group on fields selected with '*' (t).

just give you more detail of my problem

I have 2 tables: forum_thread, forum_post

forum_thread contains:
thread_id
forum_id
thread_author
thread_subject
thread_date
thread_replied

forum_post contains:
post_id
thread_id
forum_id
post_author
post_message
post_date

I need to search post_message and thread_subject
and display the result (show the subject only), just like other forum.

what happens if you do this

Error Type:
Microsoft JET Database Engine (0x80040E21)
You tried to execute a query that does not include the specified expression 'thread_date' as part of an aggregate function.

so something like...

select forum_thread.thread_subject from forum_thread inner join forum_post on forum_thread.thread_id = forum_post.thread_id where forum_post.post_message LIKE '%test%' or forum_thread.thread_subject LIKE '%test%'

This one will work
but It displays duplicate result if I have more than one post which have the search words.

I need to group by same thread and display the subject with the link.
This is the part I can't work out

nah you don't....

thanks, works