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

Matthew Stevenson
Matthew Stevenson
4,877 Points
Posted by Matthew Stevenson
Matthew Stevenson
4,877 Points

I'm trying to take Task #3 a little further by returning only the most recent loan for each book/patron.

I have the following query at the moment:

SELECT bk.title,
  pt.first_name || " " || pt.last_name AS "Name",
  pt.email,
  ln.loaned_on AS "Loaned On",
  ln.return_by AS "Return By",
  ln.returned_on AS "Returned On"
  FROM patrons AS pt
  INNER JOIN loans AS ln ON pt.id = ln.patron_id
  INNER JOIN books AS bk ON bk.id = ln.book_id
  GROUP BY bk.id, pt.id
  ORDER BY pt.first_name, bk.title, ln.loaned_on;

The ORDER BY ln.loaned_on doesn't seem to be working. To investigate this I ran another query:

SELECT bk.title, pt.id,
  pt.first_name,
  pt.last_name,
  ln.loaned_on, 
  date(ln.loaned_on),
  strftime("%Y",ln.loaned_on), 
  ln.return_by, 
  ln.returned_on 
  FROM loans AS ln
  INNER JOIN books AS bk ON ln.book_id = bk.id
  INNER JOIN patrons AS pt ON pt.id = ln.patron_id
  WHERE bk.id = 13
  AND pt.id = 3
  ORDER BY ln.loaned_on;

For the ln.loaned_on column I get back

2016-12-1

2016-12-1

2016-12-11

Because the days (<10) are not formatted as double digit (i.e. 01) the ORDER BY doesn't work.

date(ln.loaned_on) and strftime("%Y",ln.loaned_on) only work for "2016-12-11". The other two come back blank for those columns.

Could anyone shed some light on this for me?

Thanks, Matthew

KRIS NIKOLAISEN
KRIS NIKOLAISEN
54,970 Points

What course is this?

4 Answers

KRIS NIKOLAISEN
KRIS NIKOLAISEN
54,970 Points

So I was using a different database last night - one that handles dates properly :) - but am running the SQLite playground now. Yes you can create views. However I modified how loaned_on is handled to include a leading zero for the day because it is treated as text. I'm assuming the same would have to be done for the month but not necessary right now with the data provided. New view:

CREATE VIEW qryMax AS SELECT loans.patron_id, MAX(substr(loaned_on,1,8) || substr('0' || substr(loaned_on,9),-2)) AS most_recent FROM loans GROUP BY loans.patron_id;
KRIS NIKOLAISEN
KRIS NIKOLAISEN
54,970 Points
KRIS NIKOLAISEN
KRIS NIKOLAISEN
54,970 Points

I think this is where you start getting into subqueries/views. To get the most recent loan date I used the MAX function on loaned_on date, grouped by patron id and saved it as (view) qryMax

SELECT loans.patron_id, Max(loans.loaned_on) AS most_recent FROM loans GROUP BY loans.patron_id;

qryMax now provides a list of patron IDs with their most recent loan date that can be used like any other table. From here you can join the remaining tables to provide the report:

SELECT books.title, patrons.first_name, patrons.last_name, qryMax.most_recent FROM patrons INNER JOIN (books INNER JOIN (loans INNER JOIN qryMax ON (loans.loaned_on = qryMax.most_recent) AND (loans.patron_id = qryMax.patron_id)) ON books.ID = loans.book_id) ON patrons.ID = loans.patron_id;

SQLite, the DB at treehouse has views available but I haven't tried to create one in the workspace. Here's a link to a tutorial and the SQLite documentation

KRIS NIKOLAISEN
KRIS NIKOLAISEN
54,970 Points
KRIS NIKOLAISEN
KRIS NIKOLAISEN
54,970 Points

As far as date functions are concerned: From the SQLite documentation

These functions only work for dates between 0000-01-01 00:00:00 and 9999-12-31 23:59:59 (julidan day numbers 1721059.5 through 5373484.5). For dates outside that range, the results of these functions are undefined.

Try the following queries:

SELECT date(loaned_on), loaned_on FROM loans WHERE date(loaned_on) IS NULL;
SELECT date(loaned_on), loaned_on FROM loans WHERE date(loaned_on) IS NOT NULL;
Matthew Stevenson
Matthew Stevenson
4,877 Points
Matthew Stevenson
Matthew Stevenson
4,877 Points

Thank you Kris. Really appreciate the responses and sorry I couldn't get back sooner.

The course is Querying Relational Databases > Joining Table Data with SQL > Review and Practice - https://teamtreehouse.com/sql_playgrounds/502#/.

Everything you explained makes sense, though I wonder why the dates are not formatted normally as YYYY-MM-DD in this instance of the Treehouse SQL playground.

I can see how creating views could be very useful, but I'm still getting my head around this and the way you joined the tables and views in your example using nested brackets.

For now I managed to get exactly what I was going for using the MAX and substr functions.

SELECT bk.title,
   pt.first_name || " " || pt.last_name AS "Name",
   pt.email,
   MAX(substr(ln.loaned_on,1,8) || substr("0" || substr(ln.loaned_on, 9),-2)) AS "Loaned On",
   ln.return_by AS "Return By",
   ln.returned_on AS "Returned On"
   FROM patrons AS pt
   INNER JOIN loans AS ln ON pt.id = ln.patron_id
   INNER JOIN books AS bk ON bk.id = ln.book_id
   GROUP BY bk.id, pt.id
   ORDER BY pt.first_name, bk.title;