Joins

--Joins
--cross join-a cross join basically
--joins every record from one table
--with every single record in the next
--rare sometimes happen by accident

use Community_Assist
Select * from GrantType

Select PersonLastname, GrantTypeName
From Person
Cross join GrantType
order by PersonLastName

Select PersonLastName, GrantTypename
From Person, GrantType

--accidental cross
Select PersonLastName, GrantTypename, GrantRequestDate, GrantRequestAmount
From Person, GrantType, GrantRequest
Where Person.PersonKey =GrantRequest.Personkey
--And Granttype.GrantTypeKey =GrantRequest.GrantTypeKey

--inner
Select * from Employee

--inner joins return all the matching records
Select Person.Personkey, EmployeeKey, PersonLastName, PersonFirstname, EmployeeHireDate, EmployeeAnnualSalary
From Person
inner join Employee
On Person.PersonKey=Employee.PersonKey

Select * from person where personkey =5

--with table aliases and leaving out inner
Select p.Personkey, EmployeeKey, PersonLastName, PersonFirstname, EmployeeHireDate, EmployeeAnnualSalary
From Person p
join Employee e
On p.PersonKey=e.PersonKey

Select PersonLastName, PersonFirstname, PositionName, EmployeeHireDate, EmployeeAnnualSalary
From Person p
join Employee e
On p.PersonKey=e.PersonKey
inner join EmployeePosition ep
on ep.EmployeeKey=e.EmployeeKey
Inner join Position pos
on pos.PositionKey=ep.PositionKey

--outer join returns everything from one table
--whether it is matched or not
--good for finding unmatched data

Select * from GrantType
Select* from GrantRequest

--left outer join
Select GrantTypeName, GrantRequestKey
From GrantType gt
left outer join GrantRequest gr
on gt.GrantTypeKey=gr.GrantTypeKey
where grantrequestkey is null

--right outer join
Select GrantTypeName, GrantRequestKey
From GrantRequest gr
Right outer join GrantType gt
on gt.GrantTypeKey=gr.GrantTypeKey
where grantrequestkey is null

--full join
Select GrantTypeName, GrantRequestKey
From GrantRequest gr
full join GrantType gt
on gt.GrantTypeKey=gr.GrantTypeKey

Select GranttypeName, count(GrantRequestAmount) [Count],
Sum(GrantRequestAmount) Total, 
Avg(grantRequestAmount) Average
From GrantRequest gr
join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
Group by GrantTypeName

Select GranttypeName, count(GrantRequestAmount) [Count],
format(Sum(GrantRequestAmount),'$#,##0.00') Total, 
format(Avg(grantRequestAmount),'$#,##0.00') Average
From GrantRequest gr
join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
Group by GrantTypeName