Joins

Use Community_Assist

Select * from Employee
--basic inner join syntax
Select PersonLastName, PersonFirstName,
EmployeeHireDate, EmployeeAnnualSalary
From Person
Inner Join Employee
On Person.PersonKey=Employee.PersonKey

--alternate, older syntax
Select PersonLastName, PersonFirstName,
EmployeeHireDate, EmployeeAnnualSalary
From Person, Employee
Where person.PersonKey=Employee.PersonKey

--cross joins, cartesian join
--old syntax, easy to do by accident
--a cross join matches every record from the first
--table with every record in the second table
Select PersonLastName, PersonFirstName,
EmployeeHireDate, EmployeeAnnualSalary
From Person, Employee
Order by PersonLastName

--new syntax (explicit cross join)
Select PersonLastName, PersonFirstName,
EmployeeHireDate, EmployeeAnnualSalary
From Person 
Cross join Employee
Order by PersonLastName

--multi table joins
Select * from GrantRequest

Select GrantRequestDate, PersonLastName, 
GrantTypeName, GrantRequestAmount
From GrantRequest
inner Join Person
On Person.PersonKey=GrantRequest.Personkey
Inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey

--with table alias
Select GrantRequestDate, p.PersonKey, PersonLastName, 
GrantTypeName, GrantRequestAmount
From GrantRequest gr
inner Join Person p
On p.PersonKey=gr.Personkey
Inner join GrantType gt
on gt.GrantTypeKey=gr.GrantTypeKey

--with functions and aggregate functions
Select Year(GrantRequestDate) [Year],
GrantTypeName, Sum(GrantRequestAmount) Total
From GrantRequest gr
inner Join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
Group by Year(GrantRequestDate), GrantTypeName
Order by [Year]

--outer joins
--left outer join returns everything from 
--the first table listed and only matching records
--from the second table
Select Distinct gt.GrantTypeName, gr.GrantTypeKey
From GrantType gt
left outer join GrantRequest gr
on gt.GrantTypeKey=gr.GrantTypeKey
Where gr.GrantTypeKey is null

--right outer join
--same but reversed
Select Distinct gt.GrantTypeName, gr.GrantTypeKey
From GrantRequest gr
right outer join GrantType gt
on gt.GrantTypeKey=gr.GrantTypeKey
Where gr.GrantTypeKey is null

--full join returns everything from both tables
Select Distinct gt.GrantTypeName, gr.GrantTypeKey
From GrantType gt
full join GrantRequest gr
on gt.GrantTypeKey=gr.GrantTypeKey
Where gr.GrantTypeKey is null

---

Here is the code for updating the MetroAlt ridership table. Download the riders.txt from Canvas files. Right click on MetroAlt, Select TASKS/IMPORT FLAT FILES. Find Riders.Text, just click through until loaded. Then run this insert

Insert into Ridership([BusScheduleAssigmentKey],
[Riders])
Select BusScheduleAssignmentKey, Riders from [Riders]

Then run this Update Code

Use MetroAlt
Go
Alter Table ridership
Add FareKey int
Go
update ridership
set Farekey=1
Where [BusScheduleAssigmentKey] between 1 and 104348
Go
Update Ridership
set FareKey = 2
Where [BusScheduleAssigmentKey] between  104349 and 235028
Go
Update Ridership
set FareKey = 3
Where [BusScheduleAssigmentKey] between 235029 and 365708
Go
Update Ridership
set FareKey = 4
Where FareKey is Null