Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Nabil Arkate
PLUS
Nabil Arkate
Courses Plus Student 9,940 Points
Posted by Nabil Arkate
Nabil Arkate
Courses Plus Student 9,940 Points

SQL solution for the query

-- Create a report that shows the title of the book, first and last name of the patron, email and all date fields of the loan.

SELECT bk.title, pa.first_name, pa.last_name, pa.email, lo.loaned_on, lo.return_by, lo.returned_on FROM books AS bk INNER JOIN loans AS lo ON bk.id = lo.book_id INNER JOIN patrons AS pa ON pa.id = lo.patron_id ORDER BY bk.title;

Is this the solution for the question asked.

Jan Svensson
Jan Svensson
10,005 Points
Jan Svensson
Jan Svensson
10,005 Points

The question seems to suggest that it is only a report of a specific loan. You are creating a report of all the books in the database.

1 Answer

kw8
kw8
13,271 Points
kw8
kw8
13,271 Points

This is way late, but I just got to this problem and I figured it out! I did some searching online (https://javarevisited.blogspot.com/2012/11/how-to-join-three-tables-in-sql-query-mysql-sqlserver.html) found that in google searching "can you join 3 tables in SQL?"

Here is some of the text that helps put it in context:

"Three table JOIN syntax in SQL Here is a general SQL query syntax to join three or more tables :

SELECT t1.col, t3.col FROM table1 join table2 ON table1.primarykey = table2.foreignkey JOIN table3 ON table2.primarykey = table3.foreignkey;

"

So for this question I did:

"SELECT patrons.first_name, patrons.last_name, patrons.email, books.title, loans.loaned_on, loans.return_by, loans.returned_on FROM patrons JOIN loans ON patrons.id = loans.patron_id JOIN books ON loans.book_id=books.id; "

and it seems to provide a single table with the requested columns from the 3 different tables all in one. Notice the second JOIN clause is just tacked right on to the end of the first JOIN clause. I don't know if this is 100% correct structure, but the result seems to be exactly what the question is asking for: information about all books loaned out from all 3 tables. Hope this helps.