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

Marcia Haledjian
Marcia Haledjian
7,562 Points
Posted by Marcia Haledjian
Marcia Haledjian
7,562 Points

Database JOIN

Can anyone tells me if this is the correct statement?

-- 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 books.title, patrons.first_name, patrons.last_name, patrons.email, loans.book_id, loans.patron_id, loans.loaned_on, loans.return_by, loans.returned_on from patrons INNER JOIN books, loans ON patrons.id = books.id;

3 Answers

Steven Parker
Steven Parker
231,271 Points
Steven Parker
Steven Parker
231,271 Points

:point_right: It looks like you have a few things to fix yet, here's some hints:

  • you seem to be selecting a few more things than the challenge asked for
  • your join seems to be trying to include too many tables at once
  • your join operation seems to be trying to match two unrelated fields

To get the most specific and accurate answers to your questions, be sure to always provide a link to the challenge itself.

Eszter Adam
Eszter Adam
9,175 Points
Eszter Adam
Eszter Adam
9,175 Points

What about this? SELECT first_name,last_name, email, title, book_id, patron_id, loaned_on, return_by, returned_on FROM loans INNER JOIN patrons ON loans.patron_id = patrons.id INNER JOIN books ON loans.book_id = books.id;

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.