Shantanu's Blog

Joining 3 tables:
I'm relatively familiar with SQL syntax but am not sure about the use of JOIN.

Here's what I have... I have a database of a collection of books. There are three tables: a primary table containing the majority of information about the books; a table containing a list of "subject ID's" versus "subject descriptions" (example, one record states that subject_id = 1 means "mystery"); and a table containing multiple records per book ID specifying which subjects that book belongs to.

Here's a basic, simple set of data to give the idea of what I have:

Table books
fields Book_id, Author, Title, Description
Table subjects
fields Subject_id, Subject_desc
Table sub_lookup
fields book_id, Subject_id

Let's say in books we have this data:
1,"MySQL Team","How to use MySQL","This book discusses the basics of MySQL."
2,"PHP.Net","Beginner's PHP","This book describes PHP."
3,"SQL Developers Committee","Buying SQL Servers","This book describes good MySQL servers for purchase."

In subjects:
1,"Computing"
2,"MySQL"
3,"PHP"
4,"Shopping"

In sub_lookup:
1,1
1,2
2,1
2,3
3,2
3,4

Now, I want to be able to search like this pseudocode: Show me every book from the books table, where its entry in the sub_lookup table indicates the book is about computing. So if I asked for all books that are of the Computing subject, I'd want back books #1 and #2. If I asked for all books about MySQL, I'd want back books #1 and #3. If I asked for books about shopping, I'd only want book #3. And so on.

Also, it'd be convenient to not have to do a query each time I get a subject ID out of the sub_lookup table just to get its plaintext equivelant.

I know you can use JOIN to accomplish things like this, but I'm not sure where to start with it.

Advice please?

http://forums.mysql.com/read.php?10,78552,78552#msg-78552