Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2004
    Posts
    8

    Unanswered: SQL query from Mysql -> Access

    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%''.

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    try this...
    Code:
    select t.tid , 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

  3. #3
    Join Date
    Oct 2004
    Posts
    8
    Quote Originally Posted by rokslide
    try this...
    Code:
    select t.tid , 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
    Error:
    Cannot group on fields selected with '*' (t).

  4. #4
    Join Date
    Oct 2004
    Posts
    8
    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.

  5. #5
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    what happens if you do this
    Code:
    select t.tid 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

  6. #6
    Join Date
    Oct 2004
    Posts
    8
    Quote Originally Posted by rokslide
    what happens if you do this
    Code:
    select t.tid 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

    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.

  7. #7
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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%'

  8. #8
    Join Date
    Oct 2004
    Posts
    8
    Quote Originally Posted by rokslide
    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

  9. #9
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    nah you don't....
    Code:
    select distinct 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%'

  10. #10
    Join Date
    Oct 2004
    Posts
    8
    Quote Originally Posted by rokslide
    nah you don't....
    Code:
    select distinct 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%'
    thanks, works

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •