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 trialDonnie Driskell
2,243 PointsRelational 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
231,184 PointsHere'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 aUNION ALL
) - for this task you only need the "title" column from each table
- remember to include a
COUNT
Donnie Driskell
2,243 PointsThank you again Steven. Sorry for the late response. I will try that again. Donnie
Amitai Blickstein
6,863 PointsPlease 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
7,190 PointsSELECT 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;