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 Querying Relational Databases Joining Table Data with SQL Review and Practice

Christian Scherer
Christian Scherer
2,989 Points
Posted by Christian Scherer
Christian Scherer
2,989 Points

Task #3 solution

It seems like my solutions is working. If so, is there a more elegant way of solving it, e.g. a command to select all "date" columns?

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

11 Answers

Steven Parker
Steven Parker
231,248 Points
Steven Parker
Steven Parker
231,248 Points

You cannot select columns by "type".

But if you wanted all columns from the loans table you could reference them as "ln.*" in the SELECT list.

Kirill Druzhynin
seal-mask
.a{fill-rule:evenodd;}techdegree
Kirill Druzhynin
Python Web Development Techdegree Student 4,874 Points
Kirill Druzhynin
.a{fill-rule:evenodd;}techdegree
Kirill Druzhynin
Python Web Development Techdegree Student 4,874 Points

If we are talking about a report for user I think we should do like this:

SELECT BKS.title AS "Book Title", PTRS.first_name AS "First Name", PTRS.last_name AS "Last Name", PTRS.email  AS "Email", LNS.loaned_on AS "Loaned on", LNS.return_by AS "Return by",   LNS.returned_on AS "Returned on" 
  FROM books AS BKS
    INNER JOIN patrons AS PTRS on LNS.patron_id = PTRS.id
    INNER JOIN loans AS LNS ON LNS.book_id = BKS.id
      ORDER BY PTRS.last_name, PTRS.first_name;

But if the main goal is to get data in limited time I like Cedrick Lefebvre's variant :)

Thomas Lee
Thomas Lee
6,002 Points
Thomas Lee
Thomas Lee
6,002 Points

Justin, nice trick with combining the patrons.first_name and patrons.last_name with ||' '||!

Christian Scherer
Christian Scherer
2,989 Points
Christian Scherer
Christian Scherer
2,989 Points

Thanks for your quick reply Steven. OK, understood.

Aymen Hachicha
Aymen Hachicha
12,653 Points
Aymen Hachicha
Aymen Hachicha
12,653 Points

I have done it like this: 

SELECT bk.title, pt.first_name, pt.last_name, ls.* FROM loans AS ls
INNER JOIN books AS bk ON bk.id = ls.book_id
INNER JOIN patrons AS pt ON pt.id = ls.patron_id
GROUP BY first_name, email;
Justin Miller
Justin Miller
2,002 Points
Justin Miller
Justin Miller
2,002 Points

Hello Aymen, Your query is being restricted at the patron_id column from accessing the full report. As there are 5 patrons in the patrons table its only pulling the 4 with loaned records.

For reference here is my string. Hope that helps.

SELECT B.title AS Title, P.first_name || ' ' || P.last_name AS Name, P.email AS Email, L.Loaned_on AS Loaned, L.Return_by AS Due, L.Returned_on AS Returned 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;

Tom Crow
Tom Crow
719 Points
Tom Crow
Tom Crow
719 Points

But does it matter that it’s only pulling patrons that have recorded loans? My understanding of the requirements of the query was that we needed to see a list of books, followed by the people who loaned them and the associated dates. If someone has never loaned a book, that book wouldn’t need to be returned as it’s never been loaned. Also, if someone has never loaned a book, they wouldn’t show up in a report of loaned books as they’ve not yet been involved in that activity. Unless the requirement is to present all books whether they’ve been loaned or not, or all patrons whether they’ve loaned a book or not, which the questions doesn’t really make clear.

Cedrick Lefebvre
Cedrick Lefebvre
4,877 Points
Cedrick Lefebvre
Cedrick Lefebvre
4,877 Points

Hello !

An easier way of writing this code would be : 

SELECT b.title, p.first_name, p.last_name, p.email, l.loaned_on, l.return_by, l.returned_on
FROM patrons as p, books as b, loans as l 
WHERE p.id= l.patron_id
AND l.book_id= b.id
Steven Parker
Steven Parker
231,248 Points
Steven Parker
Steven Parker
231,248 Points

It's slightly more compact, but the JOIN keyword method is usually recommended as it makes the intention of the code clearer.

Cedrick Lefebvre
Cedrick Lefebvre
4,877 Points
Cedrick Lefebvre
Cedrick Lefebvre
4,877 Points

Why would we need a clearer intention in a SQL code if the result is the same but easier to code? I would understand for another programming language as we are sometimes read by someone else but here ?

Steven Parker
Steven Parker
231,248 Points
Steven Parker
Steven Parker
231,248 Points

You're right that it's not important if you're the only one who will ever see the code. The value would be in developing the habit of using the modern syntax.

Tom Crow
Tom Crow
719 Points
Tom Crow
Tom Crow
719 Points

I’m learning this for use in my work and I’ll be working in a team of people. There’s every chance someone in my team will have to run any SQL queires I design and potentially modify them in future. So in that scenario it would make sense to code as clearly as possible. The last thing I want is an email from someone else in the business asking me what the hell my code means, even if they can see what it does lol.

Giuseppe Ardito
Giuseppe Ardito
14,130 Points
Giuseppe Ardito
Giuseppe Ardito
14,130 Points

Given that the intention of this report is to have an overview of all the loans, I did like this:

SELECT ln.id, books.title, pt.first_name || ' ' || pt.last_name AS Name, pt.email, ln.loaned_on, ln.return_by, ln.returned_on
      FROM loans AS ln
             INNER JOIN patrons AS pt ON pt.id = ln.patron_id
             INNER JOIN books ON books.id = ln.book_id
      ORDER BY loaned_on DESC;
Mohammad Bazarbay
Mohammad Bazarbay
1,967 Points
Mohammad Bazarbay
Mohammad Bazarbay
1,967 Points

I thought the question was a little confusing but it is asking for the title, first and last name, email, and all the date fields from the loans table.

So when you write your query you should get almost everything (except Craig because he does not have a loan). Here is my solution.

SELECT bo.title AS Title, pt.first_name || " " || pt.last_name AS Name, pt.email AS Email, lo.loaned_on, lo.return_by, lo.returned_on FROM patrons AS pt INNER JOIN loans AS lo ON pt.id = lo.patron_id INNER JOIN books AS bo ON lo.book_id = bo.id;

Carlos Reyes
Carlos Reyes
30,056 Points
Carlos Reyes
Carlos Reyes
30,056 Points

We all are using INNER JOIN, I also did.

How can we be sure about that? I will watch again the video on outer join!

Guilherme Mergulhao
Guilherme Mergulhao
4,002 Points
Guilherme Mergulhao
Guilherme Mergulhao
4,002 Points

This is how I achieved this:

SELECT pa.first_name || ' ' || pa.last_name AS full_name, pa.email, bk.title, 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 lo.patron_id = pa.id

ORDER BY full_name, lo.loaned_on;

BigBaby Blad
BigBaby Blad
1,624 Points
BigBaby Blad
BigBaby Blad
1,624 Points

Hi,

For my report i'd like to modify the dates using the STRFTIME function such as :

STRTIME("%d/%m/%Y", l.loaned_on) AS "Loaned On" STRTIME("%d/%m/%Y", l.return_by) AS "Deadline for Return" STRTIME("%d/%m/%Y", l.returned_on) AS "Date of Return"

but it doesn't modify dates where day is between 1 and 9 and it leaves me with blank fields

How could I do ?

Are the dates in the loans table correctly inserted ? Loan -> loaned_on = "2016-12-1" Is that because the date should be "2016-12-01" ?

thanks for your help