Multi-Table MySQL Query Help

Hiya!

I have a database that holds information for an online book shop.
Data for the book shop is split into 3 tables, these are the following:

Books: Contains book information, e.g. Book Title, Book Price etc.
Customers: Contains customer information, e.g. Customer Name
Purchases: Contains purchase information, Purchaser Name, Book Purchased etc.

How do I create a query that enables information for a certain purchased book to be displayed?
A book_id will be passed using $_GET, to display all purchase information for the selected book.

Also, if a customer has purchased a book more than once, how do I display only the most recent purchase information for that particular customer, along with details for each customer that has purchased the book only once?

This is the query I have so far (Is this the best way to tackle this?):

SELECT customer_name,
       book_title,
       book_price,
       purchase_date
FROM customers,
     purchases,
     books
WHERE customers.customer_id = purchases.customer_id
  AND books.book_id = purchases.book_id

Code (markup):

Any help is greatly appreciated.
If you need more information, please let me know.

Cheers!

 

Your question seems a little contradictory--do you want all purchase information or just the most recent one? To get the most recent purchase you're going to need a GROUP BY clause and reconfigure your purchase_date:

SELECT customer_name,
       book_title,
       book_price,
       MAX(purchase_date) AS recent_purchase
FROM customers,
     purchases,
     books
WHERE customers.customer_id = purchases.customer_id
  AND books.book_id = purchases.book_id
GROUP BY customer_name, book_title, book_price;

PHP:

 

Hi Plog, thanks for your reply it works a treat.

My apologies if the question appeared to be a little contradictory, only I wanted to display all sales for a particular item, but if a customer had purchased the item more than once then I only want to know the information for the most recent purchase, a long with all purchases that had only been purchased just the once.

How would I change the query slightly so that the Purchase ID can be displayed also. I have tried adding "purchase_id" after the SELECT, but it doesn't work correctly. The purchase ID displayed doesn't match against the correct book_title, book_price etc.
Also, how would I change the query so that the information for a different product can be displayed just by changing the appropriate id in the URL.

Thanks for your time on this, it's much appreciated!