The Bookworm Turns - MySQL

So that's the theory - now let's see how it plays out in real life. Consider the following tables, which contain data for a small lending library:

+----+-------------------------------------+
| id | title                               |
+----+-------------------------------------+
|  1 | Mystic River                        |
|  2 | Catch-22                            |
|  3 | Harry Potter and the Goblet of Fire |
|  4 | The Last Detective                  |
|  5 | Murder on the Orient Express        |
+----+-------------------------------------+

This table, named "books", contains a complete list of books available for lending. Each book has a unique ID.

+-----+-------+
| id  | name  |
+-----+-------+
| 100 | Joe   |
| 101 | John  |
| 103 | Dave  |
| 109 | Harry |
| 110 | Mark  |
| 113 | Jack  |
+-----+-------+

This table, named "members", contains a list of all the members of the library. Like the books, each member also has a unique ID.

+-----------+---------+
| member_id | book_id |
+-----------+---------+
|       101 |       1 |
|       103 |       3 |
|       109 |       4 |
+-----------+---------+

As members borrow books from the library, the "data" table, above, is updated with information on which book has been lent to which member.

Using these tables and whatever you've just learned about joins, it's possible to write queries that answer the most common questions asked of the system by its operators. For example, let's suppose I wanted a list of all the current members:

SELECT * FROM members;
+-----+-------+
| id  | name  |
+-----+-------+
| 100 | Joe   |
| 101 | John  |
| 103 | Dave  |
| 109 | Harry |
| 110 | Mark  |
| 113 | Jack  |
+-----+-------+

How about a list of all the books currently in the library catalog?

SELECT * FROM books;
+----+-------------------------------------+
| id | title                               |
+----+-------------------------------------+
|  1 | Mystic River                        |
|  2 | Catch-22                            |
|  3 | Harry Potter and the Goblet of Fire |
|  4 | The Last Detective                  |
|  5 | Murder on the Orient Express        |
+----+-------------------------------------+

How about an enquiry on Dave's account, to see which books he's currently checked out?

SELECT books.title FROM data,books WHERE data.book_id = books.id  AND
data.member_id = 103;
+-------------------------------------+
| title                               |
+-------------------------------------+
| Harry Potter and the Goblet of Fire |
+-------------------------------------+

Can we find out which members *don't* have any books checked out to them (maybe these are inactive accounts)?

SELECT members.id, members.name FROM members LEFT JOIN data ON
data.member_id = members.id WHERE data.book_id IS NULL;
+-----+------+
| id  | name |
+-----+------+
| 100 | Joe  |
| 110 | Mark |
| 113 | Jack |
+-----+------+

How about a list of all the books that are currently in stock (not checked out to anyone)?

SELECT books.title FROM books LEFT JOIN data ON data.book_id = books.id
WHERE data.book_id IS NULL
+------------------------------+
| title                        |
+------------------------------+
| Catch-22                     |
| Murder on the Orient Express |
+------------------------------+

Now, how about a list of all the books currently checked out, together with the name of the member they've been checked out to?

SELECT members.name, books.title FROM data, members, books WHERE
data.member_id = members.id AND data.book_id = books.id;
+-------+-------------------------------------+
| name  | title                               |
+-------+-------------------------------------+
| John  | Mystic River                        |
| Dave  | Harry Potter and the Goblet of Fire |
| Harry | The Last Detective                  |
+-------+-------------------------------------+

You could also accomplish the above with the following query (an outer join instead of an inner join):

SELECT members.name, books.title FROM books,members LEFT JOIN data ON
data.book_id = books.id WHERE data.member_id = members.id AND
data.book_id IS NOT NULL;

As you can see, joins allow you to extract all kinds of different information from a set of tables. Go ahead and try it for yourself - think of a question you would like to ask this system, and then write a query to answer it.

>>> More MySQL Articles          >>> More By The Disenchanted Developer, (c) Melonfire

blog comments powered by Disqus