#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    13
    Rep Power
    0
    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
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Location
    Madagascar
    Posts
    9
    Rep Power
    0
    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
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    13
    Rep Power
    0
    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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Posts
    35
    Rep Power
    15
    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
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    13
    Rep Power
    0
    xiaoguiwxd: Nice try. But I don't think mySQL supports the nesting function. Or is there a new version supporting that?

    Jinn
  10. #6
  11. No Profile Picture
    Carpe Diem
    Guest
    Devshed Newbie (0 - 499 posts)
    The way to do it would be something like :-
    Table of books (Each has a unique ID)
    Table of Catergorys (Each has a unique ID)
    Another table, that has two columns, one is a book id and one is a catergory id.

    You can have multiple book ID's all pointing to a different catergory.

    That way your search would be easier.

    Regards
    Darren http://www.php4hosting.com/ $ http://www.php4hosting.co.uk/
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    13
    Rep Power
    0
    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.

Similar Threads

  1. maysqladmin drop database syntax?
    By Slurm in forum MySQL Help
    Replies: 1
    Last Post: February 17th, 2004, 05:07 AM
  2. Help using a database and php to update a scores page.
    By deathsythe in forum PHP Development
    Replies: 0
    Last Post: February 2nd, 2004, 02:48 PM
  3. 2-dimensial array / mysql_fetch_array database problem
    By paulx82 in forum PHP Development
    Replies: 3
    Last Post: December 27th, 2003, 01:52 AM
  4. creating database & tables using ASP
    By pdxlooie in forum ASP Programming
    Replies: 2
    Last Post: December 26th, 2003, 05:27 PM
  5. distributing database reads among db slaves
    By JunkCookie in forum PHP Development
    Replies: 3
    Last Post: December 17th, 2003, 11:45 AM

IMN logo majestic logo threadwatch logo seochat tools logo