Joins (mark 3 or 4)

--Joins
--inner joins
--cross joins
--outer joins
--self join
--equi-joins
--

Use CommunityAssist

Select * from PersonAddress

--simple inner join
Select Person.PersonKey, Lastname, firstname, Street, City, State, Zip
From Person
Inner Join PersonAddress
On Person.Personkey=PersonAddress.Personkey

--alternate inner join syntax
Select p.PersonKey, Lastname, firstname, Street, City, State, Zip
From Person p
Join PersonAddress pa
On p.Personkey=pa.Personkey

--Fully qualified column Name: server.database.TableName.schema.columnName

Select p.PersonKey, Lastname, firstname, Street, City, State, Zip
From Person p, PersonAddress pa
Where p.PersonKey=pa.PersonKey

--cross join old syntax
Select p.PersonKey, Lastname, firstname, Street, City, State, Zip
From Person p, PersonAddress pa

--cross join new syntax with Cross key word
Select p.PersonKey, Lastname, firstname, Street, City, State, Zip
From Person p
cross join PersonAddress pa

--multiple table join

Select Distinct Lastname, Firstname, contactInfo as Email
From Person p
Inner join personContact pc
on p.PersonKey=pc.PersonKey
inner join Donation d
on d.PersonKey=p.PersonKey
Where ContactTypeKey=6

--multiple table join old syntax
Select Distinct Lastname, Firstname, contactInfo as Email
From Person p, PersonContact pc, Donation d
Where p.PersonKey=pc.PersonKey
And p.PersonKey=d.PersonKey
And contactTypeKey=6

--outer join
Select * from [Service]

--first table is left. Returns all the data from the Service
--table, only matching data from ServiceGrant
Select ServiceName, sg.ServiceKey
From Service s
Left outer join ServiceGrant sg
on s.ServiceKey=sg.ServiceKey
where sg.ServiceKey is null

--same query flipped to right outer join
Select ServiceName, sg.ServiceKey
From ServiceGrant sg
Right join Service s
on s.ServiceKey=sg.ServiceKey
where sg.ServiceKey is null

--full join returns all records from both tables
--whether matched or not
Select ServiceName, sg.ServiceKey
From ServiceGrant sg
full join Service s
on s.ServiceKey=sg.ServiceKey



--this creates a simple database with a single
--table employee.
--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 

Select * from Employee