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
Isis van der Plas
13,445 Pointscreating a list of unique books in the SQL playground
one of the exercises in the SQL playground with this video asks you to
-- Create a list of unique books. -- Books that are in the north or south location, but not in both locations.
I managed to get two different tables, each containing 3 unique titles, doing this:
SELECT books_north.title FROM books_north EXCEPT SELECT books_south.title FROM books_south; SELECT books_south.title FROM books_south EXCEPT SELECT books_north.title FROM books_north;
however, I can't seem to merge it into one table, when I put UNION, or UNION ALL in between the two statements, like this:
SELECT books_north.title FROM books_north EXCEPT SELECT books_south.title FROM books_south UNION SELECT books_south.title FROM books_south EXCEPT SELECT books_north.title FROM books_north;
only the second table comes up, but I want to have all 6 unique titles, not three!
Can anyone point me in the right direction?
thanks in advance!
3 Answers
Steven Parker
231,271 PointsYour set operations are all performed in order, so the last one is the EXCEPT that removes all titles that are found in the north location.
But if you restructure the second query as a sub-query, you can then merge them successfully:
SELECT title FROM books_north EXCEPT SELECT title FROM books_south
UNION
SELECT * FROM (SELECT title FROM books_south EXCEPT SELECT title FROM books_north);
James Tecco
4,194 PointsCan anyone explain why "SELECT * FROM" is needed instead of just SELECT title FROM books_south EXCEPT SELECT title FROM books_north?
I thought UNION set operation will stack the two tables. The two separate SELECT statements generate the correct tables.
Steven Parker
231,271 PointsAs I told Isis, without the sub-query, the UNION is performed first and then the final EXCEPT removes all the north location items.
Jamie W.
Courses Plus Student 3,931 PointsI'd like to try a different approach to this problem. Is there a reason why SQL won't recognize books_north.title and books_south.title as columns in the following WHERE phrase?
SELECT title FROM books_north
UNION
SELECT title FROM books_south WHERE books_north.title != books_south.title;
I get an error message saying "Error: no such column: books_north.title"
Steven Parker
231,271 PointsThe WHERE clause is applied to the nearest SELECT so it does not have access to books_north.
Eric Wilson
9,380 PointsEric Wilson
9,380 PointsThis answer worked for me. However, in the course, we haven't been introduced to sub-queries yet. So, I'm not sure where the instructor included a challenge at this level that requires us to use a sub-query.