I have the following situation: I have three tables; one for books, one for categories, and one to link the two. Every book must have at least one category, but may have more.
Now I'd like to do a search for books, sorted by their categories, except I'd like each book to only be returned once. In addition, I'd like to specify a category such that, if the book is in this category, this is the category returned; if not, any one of the book's categories may be returned.
In other words, if I have ten categories (ids 1 - 10), and I choose category 6, I'd like to write a query that will return two columns (bookid and categoryid). Every book will be represented once and only once in the result. All books in category 6 will return 6 in the categoryid column. Other books will return any one of the category ids to which the book belongs.