SQL Query Unsure how to Match Different Values from Same Table

Question

I have multiple tables that I am trying to retrieve the information in a succinct query.

Example:

Table Class

ID | Name_ID |  Class | Teacher | Student
1       1         1       N         Y
2       2         1       N         Y
3       3         1       Y         N
4       4         2       N         Y
5       5         2       N         Y
6       5         2       Y         N
7       2         3       Y         N

Table Students

ID  |  First_Name  |  Last_Name |
1      Tom            Smith
2      Mike           Collins
3      Chris          Stewart
4      Paul           McCartney
5      Ringo          Starr

Table Teachers

ID  |  First_Name   | Last_Name |
1      First          Teacher
2      Second         Teacher
3      Becky          Camp
4      Fourth         Teacher
5      Mike           Brazil

My script is as follows:

select c.id, s.first_name + ' ' + s.last_name as 'Student Name', t.first_name + ' ' + t.last_name as 'Teacher Name'
from class c
left join students s on c.name_id = s.id and c.Student = 'Y'
left join teachers t on c.name_id = t.id and c.Teacher = 'Y'

The reason I do a left join is because in my full database there are some classes without students, and there are some classes without teachers. If I do an inner join, I miss out on a bunch of class IDs that don't match both criteria. But the left join is causing duplicates of the rows and filling in NULL values for one or the other.

The result I am trying to achieve is a listing of all the students and teacher for each class. Something like this:

Class | Student Name   |  Teacher Name  |
1       Tom Smith         Becky Camp
1       Mike Collins      Becky Camp
2       Paul McCartney    Mike Brazil
2       Ringo Starr       Mike Brazil
3       NULL              Second Teacher

In my full database there are some classes without any students (teacher only) and some classes without a teacher (students only), how can I join these tables together without the duplicates occurring and still retrieve a NULL for the classes that meet the no students and no teachers?

This is what I receive:

Class | Student Name   |  Teacher Name  |
1       Tom Smith         Becky Camp
1       NULL              Becky Camp
1       Mike Collins      NULL
1       Mike Collins      Becky Camp
1       NULL              Becky Camp
1       Tom Smith         NULL
2       Paul McCartney    Mike Brazil
2       NULL              Mike Brazil
2       Paul McCartney    NULL
2       Ringo Starr       Mike Brazil  
2       NULL              Mike Brazil
2       Ringo Starr       NULL

I don't disagree the database structure is creating the problem here, but it is what I have to work with unfortunately. — Matthew631, May 23 '19 at 21:44

score 0 · Answer 1 · answered May 23 '19 at 16:59

You can split this into 3 pieces:

-- inner join query
(select c.id, s.first_name + ' ' + s.last_name as 'Student Name', t.first_name + ' ' + t.last_name as 'Teacher Name'
from class c
 join students s on c.name_id = s.id and c.Student = 'Y'
 join teachers t on c.name_id = t.id and c.Teacher = 'Y')
-- 1st left join query
union all
(select * from (
select c.id, s.first_name + ' ' + s.last_name as 'Student Name', t.first_name + ' ' + t.last_name as 'Teacher Name'
from class c
left join students s on c.name_id = s.id and c.Student = 'Y'
left join teachers t on c.name_id = t.id and c.Teacher = 'Y') q
where "Student Name" is null)
-- 2nd left join query
union all
(select * from (
select c.id, s.first_name + ' ' + s.last_name as 'Student Name', t.first_name + ' ' + t.last_name as 'Teacher Name'
from class c
left join students s on c.name_id = s.id and c.Student = 'Y'
left join teachers t on c.name_id = t.id and c.Teacher = 'Y') qq
where "Teacher Name" is null)

The first if for the non null

The 2nd is for null students

The 3rd is for null teachers

SQL Query Unsure how to Match Different Values from Same Table

Table Class

Table Students

Table Teachers

1 Answers1