Many-To-Many

use ReportBuilderTraining

---
--- cleanup from last run
---

-- need to delete this first because of the foreign keys
if object_id('evc.ReportCategory') is not null
   	drop table evc.ReportCategory
	
if object_id('evc.Report') is not null
   	drop table evc.Report
 
if object_id('evc.Category') is not null
   	drop table evc.Category

go

---
--- Create tables
---

create table evc.Report (
	 ReportID int not null 
	,Name varchar(50) null
	,constraint evc_Report_PK primary key clustered (ReportID)
)

create table evc.Category (
	 CategoryID int not null 
	,Name varchar(50) null
	,constraint evc_Category_PK primary key clustered (CategoryID)
)

create table evc.ReportCategory (
	 ID int not null identity(1,1)
	,ReportId int not null
	,CategoryId int not null
	,constraint evc_ReportCategory_PK primary key clustered (ID)
)

go

---
--- Add foreign keys
---

alter table evc.ReportCategory 
	add foreign key (ReportID) 
	references evc.Report(ReportId) 
	on delete cascade 
	on update cascade

alter table evc.ReportCategory 
	add foreign key (CategoryID) 
	references evc.Category(CategoryID) 
	on delete cascade 
	on update cascade

go

---
--- Create data
---

insert into evc.Report (ReportId, Name) values (1, 'Enrollment Counts of Diverse Students')
insert into evc.Report (ReportId, Name) values (2, 'Graduation Counts of Diverse Student')
insert into evc.Report (ReportId, Name) values (3, 'Enrollment Treds')
insert into evc.Report (ReportId, Name) values (4, 'Graduation Trends')

insert into evc.Category (CategoryId, Name) 
	values 
		 (1, 'Enrolled Students') 
		,(2, 'Graduates')
		,(3, 'Students At Risk')


go

select * from evc.Report
select * from evc.Category

insert into evc.ReportCategory (ReportId, CategoryId) 
	values 
		 (1, 1)
		,(1, 3)
		,(2, 2)
		,(2, 3)
		,(3, 1)
		,(4, 2)

go


select c.Name Category, r.Name Report
	from evc.Category c
	join evc.ReportCategory rc
	on c.CategoryID=rc.CategoryId
	join evc.Report r
	on rc.ReportId=r.ReportID
	order by 1, 2

go


--- 
--- Experiment with with cascade deletes
---

select c.CategoryID, c.Name Category, r.ReportID, r.Name Report
	from evc.Category c
	join evc.ReportCategory rc
	on c.CategoryID=rc.CategoryID
	join evc.Report r
	on rc.ReportID=r.ReportID
	order by 1, 2

delete from evc.Report where ReportID=1

select * from evc.Report
select * from evc.ReportCategory

select c.CategoryID, c.Name Category, r.ReportID, r.Name Report
	from evc.Category c
	join evc.ReportCategory rc
	on c.CategoryID=rc.CategoryID
	join evc.Report r
	on rc.ReportID=r.ReportID
	order by 1, 2

--- 
--- Experiment with with cascade updates
---

update evc.Category set CategoryId=20 where CategoryId=2

select * from evc.Category
select * from evc.ReportCategory