Joins

--joins
use CommunityAssist

--inner join
Select Firstname, Lastname, ContactInfo
From Person
Inner Join PersonContact
on person.PersonKey=PersonContact.Personkey
where ContactTypeKey=6

--join is the same as inner join
Select Firstname, Lastname, ContactInfo
From Person
Join PersonContact
on person.PersonKey=PersonContact.Personkey
where ContactTypeKey=6

--with aliases for the table
Select p.Personkey, Firstname, Lastname, ContactInfo
From Person p
Join PersonContact pc
on p.PersonKey=pc.Personkey
where ContactTypeKey=6

--old syntax for inner joins
Select person.Personkey, Firstname, Lastname, ContactInfo
From Person, PersonContact
Where Person.PersonKey=PersonContact.PersonKey
And ContactTypeKey=6

--fully qualified names
--[server name].[Database Name].[Schema name].[Table Name]

--official cross join
Select lastname, contactinfo
From Person
Cross Join PersonContact

--three table join
Select Lastname , contactInfo, DonationDate, DonationAmount
From Person
Inner Join PersonContact
on Person.PersonKey=PersonContact.PersonKey
Inner Join Donation
on Person.Personkey=Donation.Personkey
Where ContactTypeKey=1

--sum with person as the inner table
Select City, Sum(DonationAmount) as Total
From Donation
Inner Join Person
on Person.PersonKey=Donation.PersonKey
Inner Join PersonAddress
on Person.PersonKey=PersonAddress.PersonKey
Group By City

--skipping the middle table because
--donation and Personaddress both share
--the personkey
Select City, Sum(DonationAmount) as Total
From Donation
inner Join PersonAddress
on Donation.PersonKey=PersonAddress.Personkey
Group by City

--three table join old syntax
Select Lastname , contactInfo, DonationDate, DonationAmount
From Person, Donation, PersonContact
Where Person.PersonKey=Donation.PersonKey
And Person.PersonKey=PersonContact.PersonKey
And ContactTypeKey=1

--just checking
Select Lastname, DonationDate, DonationAmount
From Person
inner Join Donation
on Person.Personkey=Donation.Personkey
Where Lastname='Mann'

Select * From ServiceGrant

--left outer join. left just refers to the first table
--listed. The service table will return all its rows
--the ServiceGrant (right) table will return only matching
--rows. the services that don't have a match in the 
--service grant table will return null
Select ServiceName, ServiceGrant.ServiceKey
From Service
left outer join ServiceGrant
on Service.ServiceKey=ServiceGrant.ServiceKey
Where ServiceGrant.ServiceKey is null

--same as left outer join but the order
--of the tables is flipped
Select ServiceName, ServiceGrant.ServiceKey
From ServiceGrant
right outer join Service
on Service.ServiceKey=ServiceGrant.ServiceKey
Where ServiceGrant.ServiceKey is null

Select Distinct ServiceKey From ServiceGrant

Select ServiceKey from Service

--returns all the records from both tables 
--whether they are matched or not
Select ServiceName, ServiceGrant.ServiceKey
From ServiceGrant
full outer join Service
on Service.ServiceKey=ServiceGrant.ServiceKey
Where ServiceGrant.ServiceKey is null

--this creates a simple database with a single
--table emmployee.
--Supervisors are employees,
--the supervisor's id is the employeeid of the supervisor
--so to see which employees are supervised
--by which other employees requires a 
--self join. In a self join the tables are aliased
--as if they were two seperate tables
--and then joined with an inner join
--the employeeid is joined with the supervisor id
Create Database sample
GO
Use sample
Go
Create table Employee
(
EmployeeID int Primary key,
lastName varchar(30) not null,
Supervisior int
)
GO
Insert into Employee
Values(1,'Smith', 3)
Insert into Employee
Values(2,'Rogers', 2)
Insert into Employee
Values(3,'Johnson', null)
Insert into Employee
Values(4,'Larson', 2)
Insert into Employee
Values(5,'Standish', 3)

--the self join
Select e.LastName as employee, s.Lastname as supervisor
From employee e
Inner join employee s
on s.employeeid=e.supervisior