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

John Yzaguirre
John Yzaguirre
22,025 Points
Posted by John Yzaguirre
John Yzaguirre
22,025 Points

Subqueries 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?

Derek Gella
Derek Gella
15,215 Points
Derek Gella
Derek Gella
15,215 Points

I adjusted your query to the one below, and I believe it is a correct solution: 

SELECT first_name, email, COUNT(*) AS "Loaned Out Count" FROM patrons AS p
JOIN
(
  SELECT * FROM loans_north WHERE returned_on IS NULL
  UNION
  SELECT * FROM loans_south WHERE returned_on IS NULL
) AS t ON p.id = t.patron_id
GROUP BY email;
Greg Kaleka
Greg Kaleka
39,021 Points
Greg Kaleka
Greg Kaleka
39,021 Points

Hey 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
Umesh Ravji
42,386 Points
Umesh Ravji
Umesh Ravji
42,386 Points

Almost 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
Greg Kaleka
39,021 Points
Greg Kaleka
Greg Kaleka
39,021 Points

Careful - 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
Greg Kaleka
39,021 Points
Greg Kaleka
Greg Kaleka
39,021 Points

Here'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 :beers:

-Greg

Yash Patel
Yash Patel
1,751 Points
Yash Patel
Yash Patel
1,751 Points

Also 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
Giuseppe Ardito
14,130 Points
Giuseppe Ardito
Giuseppe Ardito
14,130 Points

Here 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
doesitmatter
12,885 Points
doesitmatter
doesitmatter
12,885 Points 
SELECT 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
Gia Hoang Nguyen
4,137 Points
Gia Hoang Nguyen
Gia Hoang Nguyen
4,137 Points

SELECT 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?