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 Subqueries Review and Practice

Danny Zhang
Danny Zhang
2,014 Points
Posted by Danny Zhang
Danny Zhang
2,014 Points

playground 'user loans'

Hi, guys I wrote this sql for playground ' user loan'

select first_name, email, books.patron_id, books.booknumber from patrons inner join (select patron_id, sum(number) as booknumber from( select patron_id, count() as number from loans_north where returned_on is null group by patron_id union select patron_id, count() as number from loans_south where returned_on is null group by patron_id) group by patron_id) as books on books.patron_id = patrons.id;

Do any of you guys have a better way to write this query? I even don't wanna read it myself.....

1 Answer

Steven Parker
Steven Parker
231,236 Points
Steven Parker
Steven Parker
231,236 Points

You're not getting the correct counts because of using UNION instead of UNION ALL. But then you can certainly also simplify it a bit by doing the filtering, aggregate and grouping only once in the outer query:

SELECT first_name, email, patrons.id, COUNT(1) as booknumber
FROM patrons
INNER JOIN
  ( SELECT patron_id, returned_on FROM loans_north
    UNION ALL
    SELECT patron_id, returned_on FROM loans_south
  ) AS books
  ON books.patron_id = patrons.id
WHERE returned_on IS NULL
GROUP BY first_name, email, patrons.id
Danny Zhang
Danny Zhang
2,014 Points
Danny Zhang
Danny Zhang
2,014 Points

Thanks, Steven. do u mind tell me how to show the code like u do?

Steven Parker
Steven Parker
231,236 Points
Steven Parker
Steven Parker
231,236 Points

Use the instructions for code formatting in the Markdown Cheatsheet pop-up below the "Add an Answer" area. :arrow_heading_down:
Or watch this video on code formatting.

Happy coding!