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 Laurence kite
Laurence kite
11,768 Points

Querying SQL by Andrew Chalkey (sql playground exercise)

I wanted to see if this is a correct solution to the sql playground exercise for the subqueries This is the link to the playground questions.

https://teamtreehouse.com/sql_playgrounds/522#/queries/0cb93e9a-58f8-45d7-bb22-b15a6cf94d35 Exercise 2

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

My solution : 

SELECT  first_name, email, COUNT(email) AS "Loan Count"  FROM patrons AS P
 INNER JOIN (SELECT * FROM loans_south UNION ALL SELECT * FROM loans_north) AS allLoans
  ON P.id = allLoans.patron_id
  WHERE returned_on IS NULL
  GROUP BY email;

Also is there an easier or better solution i am sure there lol

1 Answer

Steven Parker
Steven Parker
231,198 Points
Steven Parker
Steven Parker
231,198 Points

I think you essentially have it. :+1: But other DB's might not let you get away with including a non-aggregate in the output without naming it in the GROUP BY. And you could could optionally limit the subquery to just the fields you need:

SELECT first_name, email, COUNT(email) AS "Loan Count"
FROM patrons AS P
INNER JOIN (SELECT patron_id, returned_on FROM loans_south
            UNION ALL
            SELECT patron_id, returned_on FROM loans_north) AS allLoans
      ON P.id = allLoans.patron_id
WHERE returned_on IS NULL
GROUP BY first_name, email;
Laurence kite
Laurence kite
11,768 Points

Cheers for your help...!