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 trialChristian Scherer
2,989 PointsTask #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
231,236 PointsYou 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
Python Web Development Techdegree Student 4,874 PointsIf 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
6,002 PointsJustin, nice trick with combining the patrons.first_name and patrons.last_name with ||' '||!
Christian Scherer
2,989 PointsThanks for your quick reply Steven. OK, understood.
Aymen Hachicha
12,653 PointsI 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
2,002 PointsHello 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
719 PointsBut 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
4,877 PointsHello !
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
231,236 PointsIt's slightly more compact, but the JOIN
keyword method is usually recommended as it makes the intention of the code clearer.
Cedrick Lefebvre
4,877 PointsWhy 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
231,236 PointsYou'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
719 PointsI’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
14,130 PointsGiven 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
1,967 PointsI 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
30,056 PointsWe 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
4,002 PointsThis 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
1,624 PointsHi,
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