Correlated Subqueries

use ReportBuilderTraining

---
--- find students without an address
---

select *
	from Student s
	where not exists (
		select *
			from Address
			where StudentId=s.StudentId
	)
	-- 133 students


---
--- find students without a permanent address
---

select *
	from Student s
	where not exists (
		select *
			from Address
			where AddressTypeCode='P'
			and StudentId=s.StudentId
	)
	-- 750 students


---
--- Find most recent GPA
---

select *
	from StudentHistory
	where StudentId = 1
	order by TermId

select top 1 *
	from StudentHistory
	where StudentId = 1
	order by TermId desc


select *
	,(

		select top 1 CumulativeGPA
			from StudentHistory
			where StudentId = s.StudentId
			and CumulativeGPA is not null
			order by TermId desc

	 ) [Cumulative GPA]
	from Student s
	-- 5,000 students


-- notice how this student was handled
select *
	from StudentHistory
	where StudentId=22		



---
--- Student with an address. Local when we have it, otherwise Permanent
---

select StudentId, FirstName, LastName
	,(

		select top 1 AddressId
			from Address
			where StudentId=s.StudentId
			order by AddressTypeCode

	 ) AddressId
	from Student s


select s2.FirstName, s2.LastName, a.Street1, a.Street2, a.City, a.State, a.Zip, a.AddressTypeCode
	from (

		select StudentId, FirstName, LastName
			,(
				select top 1 AddressId
					from Address
					where StudentId=s.StudentId
					order by AddressTypeCode

			 ) AddressId
			from Student s

	) s2
	join Address a
	on s2.AddressId = a.AddressId