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 Henry Brenton
Henry Brenton
10,144 Points

Confused with Subqueries challenges - please help!

Hi all,

I've been trying to get my head around this for a good few days, and I've not had much success. Please could you help?

Ideally, I'd really appreciate advice to 'tailor' my thought process, rather than to just get the answer, as I would like to learn how to approach problems like these in future.

The first challenge I'm stuck on is below:

-- Generate a report that lists the book titles from both locations and count the total number of books with the same title.

The second is as follows:

-- Generate a report that lists a patron's first name, email and loan count for loans that haven't been returned.

I've had some ideas as to how to approach these, but so far all of my attempts have been unsuccessful.

I've found UNION ALL works well for the first one, but can't quite achieve the final result - any help would be much appreciated.

Many thanks in advance!

Cara Marco
Cara Marco
2,603 Points

I worked through the first challenge about six weeks ago with another community member's help. You can see my first attempt and his correction/suggestion at https://teamtreehouse.com/community/is-there-a-less-repetitive-more-elegant-way-to-write-this-sql-query. It may help you work through the thought process.

1 Answer

Mark Chesney
Mark Chesney
11,747 Points
Mark Chesney
Mark Chesney
11,747 Points

Hi Henry,

I've just solved this, thanks to the posted help from jaycode. Here's what I got:

SELECT p.first_name, p.email
  , ln.books_out_north, ls.books_out_south
  , ln.books_out_north + ls.books_out_south AS "Total books out"
FROM patrons AS p
LEFT OUTER JOIN
  (SELECT COUNT(*) AS books_out_north, patron_id FROM loans_north
  WHERE returned_on IS NULL GROUP BY patron_id) AS ln
  ON p.id = ln.patron_id
LEFT OUTER JOIN
  (SELECT COUNT(*) AS books_out_south, patron_id FROM loans_south
  WHERE returned_on IS NULL GROUP BY patron_id) AS ls
  ON p.id = ls.patron_id

Let me know if I can break down any step. Hope this might help!