[Jinn]

I have setup a database with books, and these books fall in categories. Each book can belong to many categories, and of course each category contains many books. Here is the tables:

table books (carry info about books with primary key bid)

table cat (carry info about each category with primary key cid)

table books_cat:
bid, cid
1 1
1 2
1 4
1 5
2 2
2 3
2 5
3 2
3 3
4 2
The example here listed 4 books:
book 1 belongs to cat 1, 2, 4, 5
book 2 belongs to cat 2, 3, 5
book 3 belongs to cat 2, 3
book 4 belongs to cat 2

Question: How to select for books belong to cat2 and cat3? (that is book 2 and 3)

Possible solution: Is there anyway to "group" the table "books_cat" by bid, and somehow combine (concatenate or group) the field cid, just like the sum function for numbers. Then I can ask if the cat2 and 3 are included in the new, combined cid field. Hope this makes sense. Because I need to setup a search form, I don't know which cat or how many cat will be picked.

Any suggestion will be appreciated. Please help me! Thank you very much!

J

[Ihari]

Supposing you have a field named Title in the books table, isn't it as easy as this ?

SELECT Books.Title
FROM Books LEFT JOIN Books_Cat ON Books.BId = Books_Cat.BId
WHERE (Books_Cat.CId = 2) OR (Books_Cat.CId = 3)

This is standard SQL code so I guess there should be no reason for it not to work on MySql.

Ihari

[Jinn]

Thank you for the reply, Ihari. But your method will select books belonging to Cat2 OR Cat3. What I want is books belonging to both Cat2 AND Cat3.

I kind of having a solution but may not be very good. Use Ihari's method to get all books in Cat2 or Cat3. Add "group by bid". Count the number of Cat each bid has. The books have 2 Cat count are what I want. This is easy to say, but may not be easy to understand (it wasn't for me). Hope this solution can be helpful to others. If you got any better idea, please reply here. Thanks.

Jinn

[xiaoguiwxd]

select a.bid
from books_cat a
where a.cid = cat2
and a.bid in (
select bid
from books_cat b
where b.cid = cat3
)

I don't know the exact syntax, but it is an idea.

Hopes to help.

------------------
Good Good Study
Day Day Up

[Jinn]

xiaoguiwxd: Nice try. But I don't think mySQL supports the nesting function. Or is there a new version supporting that?

Jinn

[Jinn]

Carpe Diem: yes, this is how I setup the tables. But my question is for something else and I have a solution for that (see post above). But it may not be a good one and I would love to hear some new ideas.