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 trialPhilip Schultz
11,437 PointsWhy does UNION ALL work in this case and not just UNION
I'm not understanding why UNION ALL works in the subquery and not just UNION. I thought UNION will not show repeating data from the two tables. I'm missing something and I don't know what it is exactly. Any help would be much appreciated.
"Generate a report that lists the book titles from both locations and count the total number of books with the same title."
SELECT genre, title, COUNT(*) FROM (SELECT title, genre FROM books_north UNION ALL SELECT title, genre FROM books_south) GROUP BY title ORDER BY title DESC;
'''
2 Answers
Steven Parker
231,184 PointsYour understanding of how UNION
works is correct, and that's exactly the reason it doesn't work here..
With UNION
, the sub-query would return only one row for each title so the outer query would not be able to get a count of duplicates.
lukej
34,222 PointsI had the same issue. There a few loan records that are exactly the same in both the south and north library location.
For some reason Andrew Chalkley went to both locations on the same day and loaned out a copy of "Congo" twice which he never returned. Almost as if he didn't want anybody to read it. Suspicious, isn't it? ;)
Philip Schultz
11,437 PointsPhilip Schultz
11,437 PointsGot it! Thanks for the quick response.