I have a table 'companies' and a table 'feedback'. Feedback has rows
with a id_company key that matched the companies.id. Think of it as
the ebay rating system.
I have a simple query that works fine:
SELECT companies.id, MIN(feedback.rating), MAX(feedback.rating),
AVERAGE(feedback.rating)
FROM companies
LEFT JOIN feedback ON companies.id = feedback.id_company
GROUP BY companies.id
I get my results... all is good.
Now I add a MATCH into the loop. I want to do a full text search on
an existing index:
SELECT
MATCH (keywords, company) AGAINST ('foo bar') as rank,
companies.id, MIN(feedback.rating), MAX(feedback.rating), AVERAGE
(feedback.rating)
FROM companies
LEFT JOIN feedback ON companies.id = feedback.id_company
GROUP BY companies.id
and again I get my data... something like:
rank id min max ave
3.444 1 0 5 5
2.234 2 0 0 0
2.345 3 0 1 1
etc
Now I want to normalize the rank field i.e. rank = rank / MAX(rank):
SELECT
MATCH (keywords, company) AGAINST ('foo bar') / MAX(MATCH (keywords,
company) AGAINST ('foo bar')) as rank,
companies.id, MIN(feedback.rating), MAX(feedback.rating), AVERAGE
(feedback.rating)
FROM companies
LEFT JOIN feedback ON companies.id = feedback.id_company
GROUP BY companies.id
Except now I still get the MATCH values not the MAX = 3.444. I've
narrowed to down to my JOIN / GROUP BY. If I get rid of the join the
MAX works or if I GROUP BY feedback.rating the MAX works but I get
duplicate companies.id results (i.e. with multiple feedback rows).
Can I do this with a single query??
Thanks,
Dan T
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]