Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Databases SQL Reporting by Example Day 3: Getting Good at Grouping Finding the Troublemaker

Abdullah Jassim
Abdullah Jassim
4,551 Points

Once the two functions: Fifth and Seventh are created, instead of using inner join, why cant we use Union ALL?

-

Ryan Colmon
Ryan Colmon
Courses Plus Student 13,791 Points

Union ALL combines both queries into one big list, so you would have a list of all students who either have 5th period science OR 7th period art. With the inner join it looks only for the records that are in both queries and disregards the rest.

1 Answer

Ryan Dainton
Ryan Dainton
17,164 Points
  SELECT STUDENTS.ID, STUDENTS.FIRST_NAME, STUDENTS.LAST_NAME
  FROM CLASSES
  JOIN SUBJECTS ON CLASSES.SUBJECT_ID = SUBJECTS.ID
  JOIN SCHEDULE ON CLASSES.ID = SCHEDULE.CLASS_ID
  JOIN STUDENTS ON SCHEDULE.STUDENT_ID = STUDENTS.ID
  WHERE SUBJECTS.NAME = 'Science' AND PERIOD_ID = '5'
  INTERSECT
  SELECT STUDENTS.ID, STUDENTS.FIRST_NAME, STUDENTS.LAST_NAME
  FROM CLASSES
  JOIN SUBJECTS ON CLASSES.SUBJECT_ID = SUBJECTS.ID
  JOIN SCHEDULE ON CLASSES.ID = SCHEDULE.CLASS_ID
  JOIN STUDENTS ON SCHEDULE.STUDENT_ID = STUDENTS.ID
  WHERE SUBJECTS.NAME = 'Art' AND PERIOD_ID = '7';

As the other Ryan said, UNION ALL will combine the two sets. However, I found INTERSECT to work, as it only gives the students common to both data sets (i.e. just Bobby Tables)