Multi-Table MySQL Query Help

[FishSword]

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

 

Any help is greatly appreciated.

If you need more information, please let me know.

 

Cheers!

[gristoi]

Could you post your table structures

[FishSword]

Thanks for the reply gristoi.

Please find my database table structures below.

 

CREATE TABLE books

  (

    book_id    INT(11) NOT NULL AUTO_INCREMENT,

    book_title VARCHAR(255) NOT NULL,

    book_price VARCHAR(255) NOT NULL,

    book_owner INT(11) NOT NULL,

    PRIMARY KEY (book_id)

  )

ENGINE=myisam

DEFAULT CHARSET=latin1;

 

CREATE TABLE customers

  (

    customer_id  INT(11) NOT NULL AUTO_INCREMENT,

    customer_name VARCHAR(255) NOT NULL,

    PRIMARY KEY (customer_id)

  )

ENGINE=myisam

DEFAULT CHARSET=latin1;

 

CREATE TABLE purchases

  (

    purchase_id  INT(11) NOT NULL AUTO_INCREMENT,

    book_id      INT(11) NOT NULL,

    customer_id  INT(11) NOT NULL,

    purchase_date DATETIME NOT NULL,

    PRIMARY KEY (purchase_id)

  )

ENGINE=myisam

DEFAULT CHARSET=latin1;

[gristoi]

ok, from your table structure this should give you what you need:

For example $_GET['book_id] = $id

SELECT
b.book_title AS Title,
b.book_price AS Price,
c.customer_name, 
p.purchase_date
FROM purchases p 
LEFT JOIN customers c ON (p.customer_id = c.customer_id) 
LEFT JOIN books b ON (p.book_id = b.book_id)
WHERE p.book_id = $id

 

If you only want to get the most recent purchase of the book then try adding this to the end of the query:

ORDER BY p.purchase_date desc
LIMIT 1

 

hope this helps

[Muddy_Funster]

ok, from your table structure this should give you what you need:

For example $_GET['book_id] = $id

SELECT
b.book_title AS Title,
b.book_price AS Price,
c.customer_name, 
p.purchase_date
FROM purchases p 
LEFT JOIN customers c ON (p.customer_id = c.customer_id) 
LEFT JOIN books b ON (p.book_id = b.book_id)
WHERE p.book_id = $id

 

If you only want to get the most recent purchase of the book then try adding this to the end of the query:

ORDER BY p.purchase_date desc
LIMIT 1

 

hope this helps

 

I think INNER joins would be more applicable, and why are you bothering to alias a straight three table query where all the names are logical references already?

[DavidAM]

Since you want the last purchase date for each customer, using LIMIT is not the way to go. Try a GROUP BY - and you don't need LEFT joins here

SELECT
b.book_title AS Title,
b.book_price AS Price,
c.customer_name, 
MAX(p.purchase_date) AS LastPurchase
FROM purchases p 
JOIN customers c ON (p.customer_id = c.customer_id) 
JOIN books b ON (p.book_id = b.book_id)
WHERE p.book_id = $id
GROUP BY b.book_title, b.book_price, c.customer_name

[gristoi]

Thanks for the correction DavidAM. I am new to this forum and should have doubled checked my query first. Your spot on about not needing a left join ( my bad)

[FishSword]

Apologies of the delay in getting back to this post.

 

I think INNER joins would be more applicable, and why are you bothering to alias a straight three table query where all the names are logical references already?

 

With regards to what "Muddy_Funster" said about the issue of having 3 straight tables, would it be possible to explain a better way of doing this, as I'm keen to learn a better technique if it is better and will execute faster. Thanks for all you help on this post so far, its great!

 

Cheers.