Joins

--Joins 

Use CommunityAssist

--simple inner join you using the inner join syntax
--you must specify how the tables relate in the on clause
--almost always primary key=foreignkey (though
--the order doesn't matter)
--inner joins return only matching records
Select Firstname, lastname, DonationAmount, donationDate
From Person
Inner Join Donation --inner join and just join are equivalent
On Person.PersonKey=Donation.PersonKey

--this is an older syntax to do the same thing
Select Firstname, lastname, DonationAmount, donationDate
From Person, Donation
Where Person.PersonKey=Donation.PersonKey;

--multiple table join
--contacttypekey 1 = home phone
--this returns fewer donors because 4 don't have
--home phones
Select Firstname, lastname, ContactInfo [phone],
DonationAmount, donationDate
From Person
Inner Join Donation
On Person.PersonKey=Donation.PersonKey
inner join PersonContact
on Person.PersonKey=PersonContact.PersonKey
Where ContactTypeKey=1

--same as above older syntax
Select Firstname, lastname, ContactInfo [phone],
DonationAmount, donationDate
From Person, Donation, PersonContact
Where person.PersonKey=donation.PersonKey
And person.PersonKey=PersonContact.PersonKey
And ContactTypeKey=1

--inner join using table aliases
Select p.personkey, Firstname, lastname, ContactInfo [phone],
DonationAmount, donationDate
From Person p
Inner Join Donation d
On p.PersonKey=d.PersonKey
inner join PersonContact pc
on p.PersonKey=pc.PersonKey
Where ContactTypeKey=1

--explicit cross join syntax
--a cross join matches each record in the 
--first named table with every record in the second one
Select Lastname, DonationDate
From Person
Cross Join Donation

--the older cross join syntax
Select Lastname, DonationDate
From Person, Donation

Select * From [Service]
--an outer join to see which grant types
--have never been granted
--an outer join returns all the records from one
--table, here the left or first table, and only
--matching records from the second (right) table
--in the result set, those from the first table
--that have no matches in the second table
--show up as nulls
Select Servicename, GrantAmount
From [Service] s
Left Outer Join ServiceGrant sg
on s.ServiceKey = sg.ServiceKey
Where GrantAmount is null

--a full outer join returns all the records from each
--table whether or not they have matching records 
--in the other 
Select Servicename, GrantAmount
From [Service] s
full Outer Join ServiceGrant sg
on s.ServiceKey = sg.ServiceKey

---

Here is a little example of self joins

--a self join joins a table with itself
--here is a little script to show
--an example

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', 3)
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