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

Emily Coltman
PLUS
Emily Coltman
Courses Plus Student 9,623 Points
Posted by Emily Coltman
Emily Coltman
Courses Plus Student 9,623 Points

What am I doing wrong with the Querying Relational Databases final practice session, please?

I have a query on the final practice playground session from this course?

The first task is "Generate a report that lists the book titles from both locations and count the total number of books with the same title."

Given that the locations each have unique books in them, a LEFT OUTER JOIN wouldn't work as this would miss out the unique books from one set, and when I tried a FULL JOIN, the playground said that a FULL JOIN wasn't supported. Presumably a UNION ALL would work to list both together, but then it's not possible to list the books from North in a separate column from the books from South... I'm stuck!

The second task is "Generate a report that lists a patron's first name, email and loan count for loans that haven't been returned."

My code was:

SELECT first_name, email, ln.id, ls.id FROM patrons AS p
INNER JOIN loans_north AS ln ON p.id = ln.patron_id
INNER JOIN loans_south AS ls ON p.id = ls.patron_id
WHERE ln.returned_on IS NULL OR ls.returned_on IS NULL;

But when I tried to put in the COUNTs, wrapping the first ln.id and ls.id in a COUNT, the result counted all the loans against the first user (Andrew) and did not show any of the other users.

What am I doing wrong, please?

cc Andrew Chalkley Ben Jakuben if you have time :)

Ben Jakuben
Ben Jakuben
Treehouse Teacher
Ben Jakuben
Ben Jakuben
Treehouse Teacher

Hi Emily Coltman , is this the question you were able to solve, or do you need any additional help here? If you have a solution, are you able to summarize it here as an answer in case anyone else comes across this?

5 Answers

Steven Parker
Steven Parker
229,732 Points
Steven Parker
Steven Parker
229,732 Points

Ok, I'll show you how I did the Book Count.

But remember, these are just practice exercises so ultimately there's no "right" answer.

So the task says "Generate a report that lists the book titles from both locations and count the total number of books with the same title." My interpretation was that only titles and counts were required, and while the inventory of both locations must be considered, there would be only one count that reflected all books of that title. So based on that understanding I wrote this query:

SELECT title, count(*) as "count"
FROM (SELECT title from books_north
      UNION ALL
      SELECT title from books_south)
GROUP BY title

I used the subquery here as the data source instead of with "IN" in a WHERE clause.

Steven Parker
Steven Parker
229,732 Points
Steven Parker
Steven Parker
229,732 Points

You might be misinterpreting the instructions.

And remember this is "Subqueries Practice", so you probably won't be needing JOIN here.

I think you have the right idea about UNION ALL for the first task, but perhaps they're not asking you to identify the locations. Perhaps when they say "book titles from both locations" they mean "all book titles regardless of location". This might also be a good place to combine a sub-query with a GROUP BY.

For task 2, if you replace the JOINs with sub-queries, you could return only the books not returned from both locations. This is once again where a GROUP BY might come in handy to associate the COUNT aggregates with the users.

Emily Coltman
PLUS
Emily Coltman
Courses Plus Student 9,623 Points
Emily Coltman
Emily Coltman
Courses Plus Student 9,623 Points

Hi both,

Thanks very much for your queries.

Ben - no, this was the query I was still stuck with :) The one on the challenge question was the one I solved myself.

Steven - thanks. The course does cover using JOIN as part of subqueries? I'm not sure why I wouldn't use it given Andrew's second two videos about subqueries both use JOINs? Grateful for your guidance.

Steven Parker
Steven Parker
229,732 Points
Steven Parker
Steven Parker
229,732 Points

I suggested you wouldn't use JOIN here for two reasons:

  • this element is titled "Subqueries Practice", so it makes sense for them to be the focus of the exercises
  • all tasks presented can be efficiently resolved using only subqueries.

While there are times when it would make sense to combine these approaches, the particular tasks given here would not be any simpler (and might become more complex) compared to using subqueries alone.

Emily Coltman
PLUS
Emily Coltman
Courses Plus Student 9,623 Points
Emily Coltman
Emily Coltman
Courses Plus Student 9,623 Points

Hi Steven - yes, you're right about the subqueries practice, but the two videos immediately preceding the practice were about creating temporary tables using JOINs, so I wouldn't be surprised to see them practised here? I'd really appreciate some guidance from either yourself or from other readers as to what I'm actually doing wrong and what would work instead? You've said that subqueries would work for this - I presume you mean subqueries using IN - would you be happy to give a full example, please? Thank you.