Joins

Use Community_Assist
--joins
Select * From Employee

--inner join
Select PersonLastName, PersonFirstName, PersonEmail,
EmployeeHireDate, EmployeeAnnualSalary
From Person
inner join Employee
On Person.PersonKey = Employee.PersonKey

--just join inner optional and aliased tables
Select p.PersonKey, 
PersonLastName, PersonFirstName, PersonEmail,
EmployeeHireDate, EmployeeAnnualSalary
From Person p
join Employee e
On p.PersonKey = e.PersonKey

Select * from EmployeePosition
Select * from Position

--4 table inner join
Select PersonLastName, PersonFirstName, PersonEmail,
EmployeeHireDate, PositionName, EmployeeAnnualSalary
From Person
inner Join Employee
on Person.PersonKey=Employee.PersonKey
inner join EmployeePosition
on Employee.EmployeeKey=EmployeePosition.EmployeeKey
inner join Position
on Position.PositionKey=EmployeePosition.PositionKey

Select PersonLastName, PersonFirstName, PersonEmail,
EmployeeHireDate, PositionName, EmployeeAnnualSalary
From Person, Employee, EmployeePosition, Position
Where Person.PersonKey=Employee.PersonKey
And Employee.EmployeeKey = EmployeePosition.EmployeeKey
And Position.PositionKey=EmployeePosition.EmployeeKey

--leave out position creates an accidental cross  join
Select PersonLastName, PersonFirstName, PersonEmail,
EmployeeHireDate, PositionName, EmployeeAnnualSalary
From Person, Employee, EmployeePosition, Position
Where Person.PersonKey=Employee.PersonKey
And Position.PositionKey=EmployeePosition.EmployeeKey

--cross join
Select PersonLastName, PersonFirstName, PersonEmail,
EmployeeHireDate
From Person, Employee
--explicit cross join
Select PersonLastName, PersonFirstName, PersonEmail,
EmployeeHireDate
From Person
Cross join Employee

--outer join
--an outer join returns all the records from one table
--and only matching records from the other
--the left table is the first table named
--the right table is the second table named
Select GrantTypeName, GrantRequest.GrantTypeKey
From GrantType
left outer join GrantRequest
On GrantType.GrantTypeKey = GrantRequest.GrantTypeKey
Where GrantRequest.GrantTypeKey is null

Select GrantTypeName, format(avg(GrantRequestAmount),'$#,##0.00') Average
From GrantType
inner join GrantRequest
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Group by GrantTypeName
having avg(GrantRequestAmount)>400