Hi all
I'm working on a book database with some special requirements. Each book is associated
with some keywords and put into a category. Category 0 is special, as this is
"Unsorted", i.e. not associated with a category (which most books are at the moment).
For thei query, let's simplify the structure and define it as follows:
CREATE TABLE books (
bookID mediumint(8) unsigned NOT NULL auto_increment,
title varchar(200) NOT NULL default '',
category mediumint(9) default '0',
score mediumint(9) NOT NULL default '0',
PRIMARY KEY (bookID)
) TYPE=MyISAM AUTO_INCREMENT=1;
With each book, there is also a score field which represents the "rating" of a book,
therefore the higher the better.
Now I would like to construct a query which does the following:
* Return all books that match a requested keyword
* All matching books from category 0
* Only one book for each category >0 if there is a match with the keyword (and
if so, the one with the highest score)
* Order the books by score
Any idea and help?
Thanks a lot
cu
reto
