* Reto Baumann
> 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
Kind of tricky, but maybe something like this could be used:
SELECT DISTINCT books.*
FROM books
LEFT JOIN books b2 ON
b2.title LIKE "%$keyword%" AND
b2.category = books.category AND
b2.score > books.score
WHERE
books.title LIKE "%$keyword%" AND
(b2.bookID IS NULL OR books.category = 0)
ORDER BY books.score;
The left join is used to check if there are any rows with a higher score for
the same category. If there is, this row is _not_ included, unless
category=0.
You will get multiple books from the same category if two or more books
share the same highest score within that category.
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
