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

Posted by Don Ricardo JR
Don Ricardo JR
9,000 Points

Relational Databases: Sql Playground challenge solutions? Stumped!!

Please provide a solution to the 3rd challenge:

-- 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.

Seems I need to query 3 tables, yet left outer join only allows 2, apparently. Here's what I got, but this doesn't provide in "one report". Tried 'union' and multiple joins as in the cheatsheet. https://github.com/treehouse/cheatsheets/blob/master/querying_relational_databases/cheatsheet.md

"select distinct first_name, Last_name, email from patrons left outer join loans on patrons.id = loans.patron_id union select loans.loaned_on, loans.return_by, loans.returned_on from loans;"

https://teamtreehouse.com/community/video:65982

Thanks!

Rune Larsen
Rune Larsen
25,877 Points

This is how I solved it and it seems to have all the fields.

select title, first_name, last_name, email, loaned_on, return_by, returned_on from patrons join books join loans;

6 Answers

Don Ricardo JR
Don Ricardo JR
9,000 Points

@ RuneLarsen Thanks, that worked!

Victoria Duan
Victoria Duan
5,860 Points
Victoria Duan
Victoria Duan
5,860 Points

This seems to work for me!

SELECT title, patrons.first_name, patrons.last_name, patrons.email, loans.loaned_on, loans.return_by, loans.returned_on
  FROM books
  INNER JOIN loans ON books.id = loans.book_id
  INNER JOIN patrons ON loans.patron_id = patrons.id;
mattcleary2
mattcleary2
19,953 Points
mattcleary2
mattcleary2
19,953 Points

No, no that doesn't work, I'm also stuck on this. If you do that it brings back 1000x more data than we need. DISTINCT doesn't seem to help either.

Marcia Haledjian
Marcia Haledjian
7,562 Points
Marcia Haledjian
Marcia Haledjian
7,562 Points

Can anyone tells me if this is the correct statement:

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;

pmacsdesigners
PLUS
pmacsdesigners
Courses Plus Student 6,583 Points
pmacsdesigners
pmacsdesigners
Courses Plus Student 6,583 Points

I used this and it seemed to work:

SELECT b.title, p.first_name, p.last_name, p.email, l.loaned_on, l.return_by, l.returned_on FROM patrons AS p INNER JOIN loans AS l ON p.id = l.patron_id INNER JOIN books AS b ON l.book_id = b.id;

irmantasdvareckas
irmantasdvareckas
4,037 Points
irmantasdvareckas
irmantasdvareckas
4,037 Points

Worked for me easy and simple:

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