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

Posted by Donnie Driskell
Donnie Driskell
2,243 Points

Relational Data Bases PlayGround Challenge: books north and south

-- Generate a report that lists the book titles from both locations and count the total number of books with the same title.

This is as far as I can get. I have racked my brain trying to figure out how to get a total with the same title. Thanks. Donnie

SELECT * FROM books_north AS n LEFT OUTER JOIN (SELECT * FROM books_south) AS s ON n.title = s.title GROUP BY n.id, s.id;

4 Answers

Steven Parker
Steven Parker
243,160 Points
Steven Parker
Steven Parker
243,160 Points

Here's a few hints:

  • you don't need a derived table to perform the JOIN
  • you don't actually need a JOIN for this task at all
  • but you might want to use a UNION (perhaps a UNION ALL)
  • for this task you only need the "title" column from each table
  • remember to include a COUNT
Donnie Driskell
Donnie Driskell
2,243 Points
Donnie Driskell
Donnie Driskell
2,243 Points

Thank you again Steven. Sorry for the late response. I will try that again. Donnie

Amitai Blickstein
Amitai Blickstein
6,863 Points
Amitai Blickstein
Amitai Blickstein
6,863 Points

Please help me improve my kludgy code! I am particularly interested in how I could

  • Clean and simplify
  • Use aliases more effectively
  • Reach the solution in alternative ways

THANKS in advance, students and gurus!

After much effort, for:

Generate a report that lists the book titles from both locations and count the total number of books with the same title.

I decided that meant:

.---------.-------------------.
| titles  |    isDuplicate    |
:---------+-------------------:
| UNION   |                   |
:---------+-------------------:
| "Total" | COUNT(duplicates) |
'---------'-------------------'

and got:

-- Book Titles indicating/counting duplicates

SELECT *,
       CASE
         WHEN all_titles.title
         IN (
              SELECT title FROM books_north
               INTERSECT
              SELECT title FROM books_south
              GROUP BY title
            )
         THEN "yes"
         ELSE "no"
       END
       AS is_duplicate
FROM   (
  SELECT title FROM books_north
   UNION
  SELECT title FROM books_south
  ) AS all_titles
   UNION
  SELECT ".total number of duplicates",
         Count(title)
  FROM   (SELECT title FROM books_north
           INTERSECT
          SELECT title FROM books_south);

For

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

I got:

-- User Loans

SELECT 
  p.first_name, 
  p.email, 
  COUNT(*) AS "#books_out"
FROM (
  SELECT * FROM loans_north
  UNION 
  SELECT * FROM loans_south
) AS all_loans
INNER JOIN 
  patrons AS p 
ON
  all_loans.patron_id = p.id
WHERE 
  returned_on IS NULL
GROUP BY 
  patron_id;
Daniel Arnost
Daniel Arnost
7,190 Points
Daniel Arnost
Daniel Arnost
7,190 Points 
SELECT first_name
       , last_name
       , patron_id
       , count(tl.patron_id) as loan_count
       , email
FROM patrons p
INNER JOIN (
       SELECT  patron_id, returned_on FROM loans_north 
       UNION ALL
       SELECT  patron_id, returned_on FROM loans_south ) as tl
on p.id = tl.patron_id
WHERE tl.returned_on Is Null
Group by patron_id
Order By patron_id;