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 Michael Nanni
Michael Nanni
7,358 Points

Am I on the right track? Counting non-returned loans for all library patrons. 

SELECT count(loaned_on), p.first_name, p.email FROM (
  SELECT loans_south WHERE returned_on IS NULL
  UNION ALL 
  SELECT count(loaned_on) FROM loans_north 
   WHERE returned_on IS NULL AS temp)
 INNER JOIN patrons AS p ON temp.patron_id = p.id
 GROUP BY p.first_name;

I'm getting a result saying that I have a syntax error around one of my aliases, but I find I'm more confused about how to set up this subquery. Conceptually I thought this task required joining a 3rd table to a subquery combining data from the loans tables. Is this the right approach?

Any tips would be greatly appreciated!

4 Answers

Michael Nanni
Michael Nanni
7,358 Points

I THINK I GOT IT! Is this it? 

SELECT COUNT(LOANS_ALL.loaned_on), p.first_name, p.email FROM (SELECT ln.loaned_on, ln.patron_id FROM loans_north AS ln 
WHERE returned_on IS Null
UNION ALL SELECT ls.loaned_on, ls.patron_id FROM loans_south AS ls
WHERE returned_on IS Null) AS LOANS_ALL
INNER JOIN patrons AS p ON p.id = LOANS_ALL.patron_id
GROUP BY LOANS_ALL.patron_id;
Steven Parker
Steven Parker
231,007 Points
Steven Parker
Steven Parker
231,007 Points

Looks good. :+1: You might want to give your aggregate column an alias name: "COUNT(LOANS_ALL.loaned_on)as loans".

Steven Parker
Steven Parker
231,007 Points
Steven Parker
Steven Parker
231,007 Points

Michael Nanni — Typically, the "best answer" selection is made on the answer that helped most. :stuck_out_tongue_winking_eye:

Steven Parker
Steven Parker
231,007 Points
Steven Parker
Steven Parker
231,007 Points

The first SELECT doesn't seem to list any fields listed to output, and it is also not followed by a FROM before the table name.

Then the table alias "temp" seems to be applied to the "WHERE" clause inside the derived table parentheses. To apply it to the table, it should be moved outside of the parentheses.

As to the structure issue, could you provide a link to the challenge page? It would help to see the objective instructions.

Michael Nanni
Michael Nanni
7,358 Points
Michael Nanni
Michael Nanni
7,358 Points

Hey Steven

Thanks so much for answering. A link to the challenge and the instructions are below.

[link](Link: https://teamtreehouse.com/sql_playgrounds/522#/queries/)

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

I saw what you pointed out in my first post and decided to start over. Here's where I'm at now, but I know it's missing something.

SELECT COUNT(ln.loaned_on), ln.patron_id, p.first_name, p.email
FROM loans_north AS ln 
INNER JOIN patrons AS p ON p.id = ln.patron_id
WHERE returned_on IS Null
GROUP BY ln.patron_id;

Here I've joined the patrons and loans_north tables so that the output gives me first name, email and the number of outstanding loans each patron has. However, the only problem is that it does not also have data from the loans south table. How do I fit that in?

Ikechukwu Arum
Ikechukwu Arum
5,337 Points
Ikechukwu Arum
Ikechukwu Arum
5,337 Points

another way to do it:

SELECT id,first_name, email, Count(id) FROM (
  SELECT * FROM patrons LEFT JOIN loans_north ON patrons.id = loans_north.patron_id WHERE loans_north.returned_on IS NULL AND patrons.id = loans_north.patron_id
  UNION ALL
    SELECT * FROM patrons LEFT JOIN loans_south ON patrons.id = loans_south.patron_id WHERE loans_south.returned_on IS NULL AND patrons.id = loans_south.patron_id)  group by id;