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 trialJohn Yzaguirre
22,025 PointsSubqueries Practice am I doing this right????
Really not too confident I am getting what he is asking for, hope this makes sense. Thank you:
-- Generate a report that lists a patron's first name, email and loan count for loans that haven't been returned.
SELECT first_name, email, COUNT(*) FROM patrons AS p
JOIN
(
SELECT * FROM loans_north
UNION
SELECT * FROM loans_south
) AS t ON p.id = t.patron_id
GROUP BY first_name;
Is this what he wanted?
Greg Kaleka
39,021 PointsHey Derek,
I mentioned this below, but FYI the only thing you're missing is that it needs to be UNION ALL, not just UNION. I explain in my other comment.
6 Answers
Umesh Ravji
42,386 PointsAlmost there, just one small issue.
SELECT * FROM loans_north
UNION
SELECT * FROM loans_south
This will return all of the loans from the two loans tables. The question wants only loans that have not been returned so you should use a WHERE clause to find only loans that have not been returned yet.
Greg Kaleka
39,021 PointsCareful - there's one other problem you're both missing: you need to UNION ALL. A couple of records are duplicated in the loans_north and loans_south tables, meaning the same person took out a book with the same id (though not necessarily the same book) on the same day from each library. If you use UNION that only counts as one book, even though it should obviously count as two. UNION ALL preserves any duplicates (note they're not really duplicates, conceptually, in this case - they're two separate transactions).
Greg Kaleka
39,021 PointsHere's an alternative solution to the one you've (almost!) got in your question. For this size database, it doesn't really matter, but if this were a different db with millions of rows, shooting off SELECT *
statements all willy nilly would probably not be a good idea, especially in a sub-query, where that table is held in memory.
All we need from the loan tables are the count of loans that haven't been returned grouped by patron_id. We'll need to UNION ALL two queries - one for north and one for south. The result of that union will give us a two column table with patron_id and a number of outstanding loans. We alias this table as loans
. In the outer query, we want the patron's name, email, and then their total loans. For the last field, we can sum the number of loans, and group by patron. Note that we can't just count, because the way we did the subquery, we got records like:
| patron_id | OutstandingLoans|
| --------- | --------------- |
| 3 | 7 |
So we need to sum them up, grouped by patron_id.
SELECT patrons.first_name, patrons.email, sum(loans.OutstandingLoans) AS "Outstanding Loans" FROM patrons
INNER JOIN (
SELECT COUNT(*) AS OutstandingLoans, patron_id
FROM loans_north WHERE returned_on IS NULL
GROUP BY patron_id
UNION ALL
SELECT COUNT(*) AS OutstandingLoans, patron_id
FROM loans_south WHERE returned_on IS NULL
GROUP BY patron_id
) AS loans ON patrons.id = loans.patron_id
GROUP BY patron_id;
Anyway, this is a slightly more complex solution, but should have better query performance on big tables.
Cheers
-Greg
Yash Patel
1,751 PointsAlso for group by you'd want to use a more unique grouping factor such as email since multiple patrons could share a first name (this applies to context beyond the patrons list in this schema)
Giuseppe Ardito
14,130 PointsHere is my solution:
SELECT first_name AS [Patron Name], email, notReturnedCount AS [Loans not returned] FROM patrons
INNER JOIN
(SELECT patron_id, COUNT(*) AS notReturnedCount FROM
-- SELECT entire list for both locations
(SELECT patron_id, returned_on FROM loans_north
UNION ALL
SELECT patron_id, returned_on FROM loans_south)
-- ONLY loans not returned, groupped and ordered
WHERE returned_on IS NULL
GROUP BY patron_id
ORDER BY notReturnedCount DESC) AS cnr
ON patrons.id = cnr.patron_id;
As you can see, to do that I had to nest a subquery into a subquery but it seems to make things more simple to read.
doesitmatter
12,885 PointsSELECT patrons.id, patrons.first_name, patrons.email, COUNT(*) as "Open loans"
FROM (
SELECT * FROM (
SELECT * FROM loans_north UNION ALL SELECT * FROM loans_south WHERE returned_on is NULL
) as S
WHERE S.returned_on IS NULL
) as S
JOIN patrons on S.patron_id = patrons.id GROUP BY patrons.first_name;
Gia Hoang Nguyen
4,137 PointsSELECT p.id, p.first_name, p.email, COUNT(id) AS OpenLoanCount FROM patrons AS p JOIN (SELECT patron_id, loaned_on, returned_on FROM loans_south UNION ALL SELECT patron_id, loaned_on,returned_on FROM loans_north ) AS loans ON loans.patron_id = p.id WHERE returned_on IS NULL GROUP BY p.id;
This is my solution. Is this code clean enough for db that contains millions of rows? is there anyway to optimize my code?
Derek Gella
15,215 PointsDerek Gella
15,215 PointsI adjusted your query to the one below, and I believe it is a correct solution: