query for selecting books based on specific categories

RoundsToZero

I'm writing a library management application. A book can fall under any number of categories, which are found in a separate table, with a categories_relation table for normalizing the M:N relationship.

For the search function of this application, one of the features is to search for books that are in either any or all of a number of categories specified by the user. I've got the query working that does the search for books in any of the specified categories, but I'm having trouble with the one that searches only for books that are in all of the specified categories.

Here's an example of the query used in the all scenario. I can see that it's wrong somehow, but I don't know how to fix it.

select distinct `books`.`book_id`
from `authors`, `authors_relation`, `books`, `categories`, `categories_relation`, `locations`, `locations_relation`
where
`authors`.`author_id` = `authors_relation`.`author_id` and
`authors_relation`.`book_id` = `books`.`book_id` and
`categories`.`category_id` = `categories_relation`.`category_id` and
`categories_relation`.`book_id` = `books`.`book_id` and
`locations`.`location_id` = `locations_relation`.`location_id` and
`locations_relation`.`book_id` = `books`.`book_id` and
((`categories`.`category_id` = '3' and `categories`.`category_id` = '1'))

The problem is definitely in the last bit where the condition is that categories.category_id has to be two category IDs at once. In the any case, the and is simply changed to an or, which works fine for that. How do I make it do what I want it to do for all?

Roger_Ramjet

OK. The post as it stands says that you want the book_id. Now you can acheive all that you wish with just the categories_relations table. I will show you the generic solution for this type of problem using just the cross-ref table. You can then use and extend it with joined tables as and when you need other fields. If your db version supports subqueries then you can use this as one and join/select in the outer query when book_id in subquery.


$query_any = "SELECT DISTINCT book_id 
FROM categories_relations 
WHERE category_id = 1 OR category_id =2";

$query_all = "SELECT DISTINCT t1.book_id 
FROM categories_relations AS t1 
LEFT JOIN categories_relations AS t2 
USING (book_id)
WHERE t1.category_id=1 AND t2.category_id=2";

To use these as subqueries, define them as strings and then


$query = "SELECT books.*, locations.* 
FROM books LEFT JOIN locations USING (book_id)
WHERE book_id IN ($query_any)";

// or $query_all as required. You can substitute $vars for my hard-coded values in the subquery defs as required

RoundsToZero

Thanks for your quick reply.

However, I'm getting the an error with the following statement. It seems like it doesn't like the fact that I'm using subqueries,
but my MySQL version is 4.0.20 which seems recent enough to have that feature. How am I off-base?

SQL-query :

SELECT DISTINCT `books`.`book_id`
FROM `authors` , `authors_relation` , `books` , `categories` , `categories_relation` , 
`locations` , `locations_relation`
WHERE `authors`.`author_id` = `authors_relation`.`author_id` AND
 `authors_relation`.`book_id` = `books`.`book_id` AND
 `categories`.`category_id` = `categories_relation`.`category_id` AND
 `categories_relation`.`book_id` = `books`.`book_id` AND
 `locations`.`location_id` = `locations_relation`.`location_id` AND
 `locations_relation`.`book_id` = `books`.`book_id` 
  AND (
  `books`.`book_id`
  IN (

SELECT DISTINCT book_id
FROM categories_relation
WHERE (
  `category_id` = '3' OR `category_id` = '1'
)
  )
)
LIMIT 0 , 30

MySQL said:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right
syntax to use near 'SELECT DISTINCT book_id
FROM categories_relation
WHERE ( `cat

Roger_Ramjet

Take the ' quotes out. This is the syntax for quoting a literal value. There is no need to use quotes on table or field names unless you have used reserved words for names, or names with embedded blanks (why??). If you do need to quote such off-the-wall names then you must use the " double quotes, not the ' single quotes.

Basically, I know of no situation that forces you to use names that require quotes. Always make your names as mainstream SQL standard as possible, ie no need for quotes. If nothing else it will make your design portable across diff db engines.

RoundsToZero

Took out all the quotes and still the same problem. Exactly what version is required for subquerys though?

Roger_Ramjet

4.1

If yours is earlier then see the manual for the work around.

Roger_Ramjet

My use of the subquery strings illustrates a common practice when using relationship matrices like your categories_relation.

Many people make the mistake of thinking that the constructed table is there to facilitate joins between the main tables. Used as they are in your design where neither of the foriegn key columns is unique, you cannot in fact go from books to categories with 2 joins in 1 query.

What one can do is to go from the book_id in the matrix to the categories table, likewise for cat_id and books. Thus, given a book_id one can find all details about all of it's categories: overcoming the problem of the M:N relation.

The matrix is just a constructed index. To facilitate it's use, the common practice is to build and store generalised queries for use throughout the project, either as subqueries or in code procedures.