MySQL Forums
Forum List  »  Newbie

Using JOIN
Posted by: Flint M
Date: March 26, 2006 04:37AM

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?
Thank you

Flint M

Options: ReplyQuote


Subject
Written By
Posted
Using JOIN
March 26, 2006 04:37AM
March 26, 2006 05:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.