[SOLVED] Need help grouping information from three tables

[zsazsageorge]

Hi, I am using the MySQL Quesry Browser 5.0

 

I am trying to join information from three tables:

 

The first table is "Customers" which includes:

Customerid

Name

Address

City

 

The second table is "books," which includes:

ISBN

Author

Title

Price

 

The third table is "orders," which includes:

Orderid

Customerid

Amount

Date

 

 

I need to display a table that shows, starting from the left, the customer name, title of their purchased book, author of their purchased book.

 

So I need to somehow link the customerid with the orderid and author/title.

 

Would anyone be able to tell me how to do that?

 

Thank you so much, I am lost.

 

 

[obsidian]

Is there a table (something like order details) that actually shows which ISBN or book ID was purchased in a given order? Without that, it is impossible to tie the book back to the order. That has to be done in order to know who purchased each book...

[zsazsageorge]

There is another table called "order_items," which includes the following:

Orderid

ISBN

Quantity

 

 

 

I hope that helps... I am totally confused.

[obsidian]

Excellent. You actually need to have all 4 tables to get your user tied to the book title and author. Run this query in MySql query browser and see what you get:

SELECT c.name, b.title, b.author 
FROM customers c
INNER JOIN orders o ON c.customerid = o.customerid
INNER JOIN order_items i ON o.orderid = i.orderid
INNER JOIN books b ON i.isbn = b.isbn

 

That should give you a list of customer names along with the author and title of the books they purchased. You may need to double check the capitalization of the tables and fields in the query as I left them all as lower case, but I hope this helps!

[zsazsageorge]

It worked, thank you so much! 

[zsazsageorge]

One more question, if you would be so kind.

 

There is another table, called "book_reviews," which includes the following:

ISBN

Review

 

This is related to the four tables listed above.

 

How would I show for each author and title in books with its associated book review?

 

Thanks again in advance... last question I swear.

[obsidian]

One more question, if you would be so kind.

That's what we're here for.

 

There is another table, called "book_reviews," which includes the following:

ISBN

Review

 

This is related to the four tables listed above.

 

How would I show for each author and title in books with its associated book review?

 

You would do a similar join, but you would only have to do the join on two tables this time:

SELECT b.author, b.title, r.review
FROM books b
INNER JOIN book_reviews r ON b.isbn = r.isbn

 

Good luck!

[zsazsageorge]

Thank you!