Re: easy question

In article <udkF4.1563$hc7.45042_at_news2-win.server.ntlworld.com>, sean_at_networkdata.co.uk writes
>Hi,
>
>Although I use MySQL - this is a basic RDBMS question.
>
>Please help as my website project cannot proceed until I understand the
>following.
>
>Example scenario.
>I have two tables - People AND books. I record personal Info in one and Book
>info in the other.
>
>I already know how to query the people table and I also know how to query
>the books table. I even know how to query both together. (Something like
>this):
>
>SELECT people.name,books.title FROM people,books WHERE people.name="fred" OR
>books.title="mysql";

That gets you every record which meets either of the conditions you specified. That's probably not what you really want to do.

>
>WHAT I REALLY WANT TO DO is query the database to find out which people have
>read the book entitled MySQL.
>
>I think I need book_id and People_id fields which are primary keys and
>auto_increment but even if I do this how does MySQL know who has read which
>books.

You need a third table. Call it readers. It will have two fields book_id and people_id. When someone reads a book, create a new record in this table. Add the keys of the book and the title they read.

SELECT people.name,books.title FROM people,books,readers WHERE people.name="fred"
and books.title="mysql"
and readers.book_id =books.book_id
and readers.people_id = people.people_id;

This gets you the names of all of the people who have read the book called "mysql". The database know who they are because only those people have the right pairing of bppok_id and people_id in the same record.

Some records in the readers table have the right book_id, some have the right people_id but only one should have both of them.

-- 
Bernard Peek
bap_at_shrdlu.com
bap_at_shrdlu.co.uk