Subquery 'loans challenge': Feedback on this solution

Question

I have created the following method to finish this challenge. I'm not sure whether or not it is correct. And from one part I'm not really sure why it works.

I'll add the code step by step to show my though process:

step 1: merge two loan tables

-- First do a check to see if the loan amount is equal (mandatory for a merge)
SELECT COUNT(*) AS "Total Loans South" FROM loans_north;
SELECT COUNT(*) AS "Total Loans North" FROM loans_south;

-- both 100 rows so can proceed

-- Then test merging of the two. Uses UNION all to make sure duplicates are included
SELECT * FROM loans_north
UNION ALL
SELECT * FROM loans_south;

step 2: make temporary/derived table

SELECT * FROM (
  SELECT * FROM loans_north
  UNION ALL
  SELECT * FROM loans_south
) AS loans_all;

Step 3: Merge the loans table with the patrons. This uses the foreign key to link the correct patron to the book. Also the subquery pattern is used.

SELECT * FROM (
  SELECT * FROM loans_north
  UNION ALL
  SELECT * FROM loans_south
) AS loans_all
LEFT OUTER JOIN patrons ON loans_all.patron_id = patrons.id;

Step 4: We need only the books that haven't been returned yet. Which is defined by the returned_on column, empty or NULL means the book hasn't been returned yet.

SELECT * FROM (
  SELECT * FROM loans_north
  WHERE returned_on IS NULL
  UNION ALL
  SELECT * FROM loans_south
  WHERE returned_on IS NULL
) AS loans_all
LEFT OUTER JOIN patrons ON loans_all.patron_id = patrons.id;

Step 5: Group by the patron, add fields which you want to show.

SELECT patrons.first_name, patrons.last_name, patrons.email, COUNT(*) AS "Loan count" FROM (
  SELECT * FROM loans_north
  WHERE returned_on IS NULL
  UNION ALL
  SELECT * FROM loans_south
  WHERE returned_on IS NULL
) AS loans_all
LEFT OUTER JOIN patrons ON loans_all.patron_id = patrons.id
GROUP BY loans_all.patron_id;

End result: A table which shows Andrew's loan count: 10 Dave's loan count: 7 Alena's loan count: 8 Michael' loan count: 3

Please let me know if you have something else or if you have feedback.

Answer 1 · 2019-07-29T15:02:02Z

July 29, 2019 3:02pm

Is a left join necessary? Left joins can be far more taxing than an inner join. Based on the questions I'm not sure if you need a left join. The "WHERE returned_on IS NULL", you have that twice for both sub queries. You could perhaps take that out from both queries in the sub query and move it outside, after the join, like a where clause to the entire query instead. Kind of a DRY code thing and it could make your query run faster.

Welcome to the Treehouse Community

Looking to learn something new?

Remi Vledder

Remi Vledder

Subquery 'loans challenge': Feedback on this solution

1 Answer

Gabriel Plackey

Gabriel Plackey