MySQL Forums
Forum List  »  Newbie

Grouping the result of a union
Posted by: Sander Marechal
Date: July 26, 2005 12:10AM

Hello everyone,

For the last couple I have been stumped by a certain MySQL query that I want to run. I am building a custom search function for my website using PHP and MySQL. In order to do that, I store words in a separate table and link them to comments, summaries and keyword fields via three separate tables. The 'words' table simply contains a list of words and their row ID. The three word match tables that I use to link words to the contents have a word ID field and a comment ID field. what I want is a query that will give me all word ID's that occur a minimum of X times in those three tables combined. I will take X as 10 for the below example query.

The query I came up with to do this is this one:

(
SELECT word_id as word_index
FROM search_match_summary
)
UNION ALL
(
SELECT word_id AS word_index
FROM search_match_comment
)
UNION ALL
(
SELECT word_id AS word_index
FROM search_match_keyword
)
GROUP BY word_index
HAVING COUNT(word_index) > 10

Unfortunately, this query does not work (on MySQL 4.0.x). It starts complaining at the GROUP BY clause. If I remove the GROUP BY and HAVING clause then the query works (so, the UNION's are okay). If I replace the GROUP BY and HAVING with "ORDER BY word_index" then the query works as well. It just refuses to group and count for me.

Can someone tell me what is wrong with my query? Can't I group unions? If not, how can I get the result I want? I prefer to do this entorely in SQL without doing post-processing of several datasets in PHP because this query is going to run on very big tables and I need speed.

Thank you all!

--
Sander Marechal

Options: ReplyQuote


Subject
Written By
Posted
Grouping the result of a union
July 26, 2005 12:10AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.