Joins

use CommunityAssist

Select LastName, Firstname, Street, [State], City, Zip
From Person
Inner Join PersonAddress
ON Person.PersonKey=PersonAddress.PersonKey

--
Select LastName, Firstname, Street, [State], City, Zip, ContactInfo
From Person, PersonAddress, personContact
WHERE Person.PersonKey=PersonAddress.PersonKey
AND Person.PersonKey=PersonContact.PersonKey

Select p.PersonKey, LastName, Firstname, Street, [State], City, Zip
From Person p
Inner Join PersonAddress pa
ON p.PersonKey=pa.PersonKey


Select LastName, Firstname, Hiredate, Dependents, Street, City,
[State], Zip
From Person p
inner join Employee e
on p.PersonKey=e.PersonKey
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey

select LastName, City
From Person
Cross join PersonAddress

Select ServiceName, GrantKey
From [Service] s
left outer join ServiceGrant sg
On s.ServiceKey=sg.ServiceKey
Where GrantKey is Null

Select ServiceName, GrantKey
From [Service] s
inner join ServiceGrant sg
on sg.GrantKey > s.ServiceKey

--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)

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