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 trialAhmed Mohamed Fouad
11,735 PointsSQL Playground - Set Operations Practice (second question)
The Question:
FIND UNIQUE BOOKS -- Create a list of unique books. -- Books that are in the north or south location, but not in both locations.
My Answer:
SELECT title FROM books_north EXCEPT SELECT title FROM books_south UNION SELECT title FROM books_south EXCEPT SELECT title FROM books_north;
and I don't think this answer is true, would you please help ?
7 Answers
Steven Parker
231,172 PointsYou're close, but you need that last EXCEPT
to occur before the UNION
. A sub-query or derived table would help:
SELECT title FROM books_north EXCEPT SELECT title FROM books_south
UNION
SELECT title FROM (SELECT title FROM books_south EXCEPT SELECT title FROM books_north);
But those techniques haven't been introduced yet. Without skipping ahead, you can do it using a JOIN
:
SELECT title FROM books_south UNION SELECT title FROM books_north
EXCEPT
SELECT books_south.title FROM books_south
INNER JOIN books_north ON books_south.title = books_north.title;
Ahmed Mohamed Fouad
11,735 PointsThanks Steven, your answers are always the best, easy to understand & straight to the point
ryanosten
PHP Development Techdegree Student 29,615 PointsIm not understanding why this query doesnt work. I see the results are only those that are in south but not in north, but Im not understanding why. Shouldnt it union both (a) those that are in north but not in south, and (b) those that are in south but not in north?
SELECT title FROM books_north EXCEPT SELECT title FROM books_south
UNION
SELECT title FROM books_south EXCEPT SELECT title FROM books_north;
ryanosten
PHP Development Techdegree Student 29,615 PointsI guess its something with order of operations
ryanosten
PHP Development Techdegree Student 29,615 Pointsnvm I see why.
kevin hudson
Courses Plus Student 11,987 PointsI might be wrong here but my answer ( after reviewing both tables manually) only produced three results which appear to be true thus the correct answer should be:
-- Create a list of unique books.
-- Books that are in the north or south location, but not in both locations.
SELECT title,author FROM books_north EXCEPT
SELECT title,author FROM books_south ORDER BY title;
Steven Parker
231,172 PointsThat gets you the 3 books in the North location that are not in the South, but there are 3 more in the South that are not in the North. The total list of unique books will have 6 items.
kevin hudson
Courses Plus Student 11,987 PointsYeah, I spoke too soon but decided to leave my answer to further help understand the problem. I believe the question feels like it's out of one's scope if you are following along you have to include an INNER JOIN somewhere and, as you stated, the subquery example will work but wasn't covered at that time. I am just now going over subqueries.
Mark Chesney
11,747 PointsMark Chesney
11,747 PointsHi Ahmed,
I really liked your answer... because I came up with the same one too...unfortunately, with no success :\
As you've probably observed (but this is for everyone else's knowledge), the following two queries return the same results:
My only solution to this same question is found in two separate queries, unfortunately: