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

Posted by Mohammad Aslam
Mohammad Aslam
6,053 Points

Need help with Set Operations practice item

"-- Create a list of unique books. -- Books that are in the north or south location, but not in both locations."

This is the task for a database with 2 tables for north and south location. How do you get the correct answer?

Union will give all books, Intersect will give common books, Except will give books unique to one location only.

Can we join to Except queries for each library?

3 Answers

Mohammad Aslam
Mohammad Aslam
6,053 Points

This question has been deleted from the Review Playground section.

Alissa Kuzina
Alissa Kuzina
5,835 Points
Alissa Kuzina
Alissa Kuzina
5,835 Points 
SELECT books_south.title FROM  books_south
  UNION
SELECT books_north.title FROM books_north
  EXCEPT 
SELECT books_south.title FROM books_south
INNER JOIN books_north ON books_south.title = books_north.title;

I don't know if it's absolutely correct so, anybody if you see it and you know it could be better you're welcome)

So my logic was that I unite two tables in title so there're no duplicates included. We have all books from both libraries. After that we have an ecxeption. I use EXCEPT and INNER JOIN. That's because if you imagine a Venn diagram you'll get common books by using INNER JOIN and after that use an EXCEPT for your final result. That query gives titles that you don't have in books_south but have in books_north and vise versa together.

Xingyu Wang
PLUS
Xingyu Wang
Courses Plus Student 3,538 Points
Xingyu Wang
Xingyu Wang
Courses Plus Student 3,538 Points 
SELECT N.* FROM books_north AS N 
LEFT JOIN books_south AS S 
ON N.title=S.title 
WHERE S.title IS NULL
UNION
SELECT S.* FROM books_south AS S 
LEFT JOIN books_north AS N 
ON N.title=S.title 
WHERE N.title IS NULL;