Hi Phil, relax you are on track! First a little theory. In this case you have the so called N-to-M relationship. While if you need to relate one book to one author(given only one author per book!) you have the so called 1-to-N relationship, in this case you have the N-to-M relationship. If you are asking yourself why one book-one author is a 1-to-N relationship do not think of the single row/record, but reason this way(again, given only one author per book):
Take one row on the [books] table and ask yourself: "How many possible authors have written this book?" Ans: 1 Take one row on the [authors] table and ask yourself: "How many possible books have been written by this authors" Ans: Many (N any number) so you have a 1-to-N relationship in your example you have [books] and [categories] Take one row on the [books] table and ask yourself: "To how many possible categories belongs this book?" Ans: Many (N) Take one row on the [categories] table and ask yourself: "How many books can belong to this category?" Ans: Many (M , N is already taken!) so you have a N-to-M relationship while to implement a 1-to-N relationship you use a Foreign Key on one table, to implement a N-to-M relationship you need to use a table (cross reference) This means that while you still have the two original tables you will build a third table that connect the former two ones. This third table(Cross reference) it is just composed, at least, by two fields(two foreign keys) one referring to the primary key of the first table [books] and one referring to the primary kay of the second table [categories]. Just insert for each book as many rows as the categories to which the book belongs, for example, if you have a book with book_id=1 and various categories(1,2,3,4,5,6,7,8,9,10), to assign to the book the categories 1,3,8 insert three rows in the cross-reference table: book_id | category_id ------------------------------- 1 | 1 1 | 3 1 | 8 AGAIN: CONCAT is just a string function, it is not a sql operator, and it is not giving anything that you don't already know, dont focus on CONCAT, forget it for now. Let me know Claudio Nanni 2009/2/22 PJ <af.gour...@videotron.ca> > Here's a tough one... > In my library I have some 62 categories where a number of books can be > in several categories. > Now, to relate the "categories" table(AS c) to the "book" table (AS a), > I assume that I need an intermediate table "book_category" (AS d) > linking d.bookID to the b.bookID and d.categoryID to c.id. With so many > categories and (probably) thousands of books d may become quite lengthy > (but, I guess it's better than 62 * 100's of lengthy fields added to b). > To enter the relationships I would add instructions on my > addNewBooks.php form with the input as multiple choice dropdown box > (listing the categories)... > so far, so good (I hope)... > Now, how do I SELECT and retrieve these categories to display on the web > page? > From the book_category table with a WHERE statement? If so, what then? > CONCAT_WS the stuff to go into the html table <td>? > I hope I'm on the right track... or am I in deep water? > Help... > > -- > > Phil Jourdan --- p...@ptahhotep.com > http://www.ptahhotep.com > http://www.chiccantine.com > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com > >
