Joins

use ReportBuilderTraining

---
--- An inner join
---

select distinct StudentId
	from Student 
	-- 5000 students

select *
	from Student
	join Address
	on Student.StudentId=Address.StudentId

select distinct Student.StudentId
	from Student
	join Address
	on Student.StudentId=Address.StudentId


---
--- Table Aliases
---

select distinct s.StudentId
	from Student AS s
	join Address AS a
	on s.StudentId=a.StudentId
	-- 4,867 students (not all have addresses)


---
--- Left outer join
---

select *
	from Student s
	left join Address a
	on s.StudentId=a.StudentId
	order by a.AddressId

select distinct s.StudentId
	from Student s
	left join Address a
	on s.StudentId=a.StudentId
	-- 5,000 students


---
--- Right outer join
---

select *
	from Address a
	right join Student s
	on a.StudentId=s.StudentId
	order by a.AddressId


---
--- Find students who don't have an address
---

select distinct s.*
	from Student s
	left join Address a
	on s.StudentId=a.StudentId
	where a.StudentId is null
	-- 133 students


---
--- Retrieve all students, with permanent address when available
---

select *
	from Student s
	left join Address a
	on s.StudentId=a.StudentId
	where (a.StudentId is null or a.AddressTypeCode='P')
	-- 4383 students, we are missing some!

select *
	from Student s
	left join Address a
	on s.StudentId=a.StudentId and a.AddressTypeCode='P'
	order by a.AddressId
	-- 5,000 students



---
--- multiple join statements
---

if object_id('evc.StudentMajor') is not null
	drop table evc.StudentMajor

go

-- remove undeclared
select *
	into evc.StudentMajor
	from StudentMajor
	where MajorCode in ('W001', 'R001')


select distinct s.StudentId
	from Student s
	left join evc.StudentMajor sm
	on s.StudentId=sm.StudentId
	-- 5,000 students

select distinct s.StudentId
	from Student s
	left join evc.StudentMajor sm
	on s.StudentId=sm.StudentId
	join Major m
	on sm.MajorId=m.MajorId
	--  1,115 students

select distinct s.StudentId
	from Student s
	left join evc.StudentMajor sm
	on s.StudentId=sm.StudentId
	left join Major m
	on sm.MajorId=m.MajorId
	--  5,000 students