Joins

--Joins--Queries across tables
--Cross Joins
Use Community_Assist

Select Person.PersonKey, PersonLastName, ContactNumber from Person 
Cross Join Contact

Select Person.PersonKey, PersonLastName, ContactNumber 
from Person, Contact 

--Inner Joins--they return matching rows from two or more tables
--old syntax
Select PersonFirstName, PersonLastName, PersonEmail,
PersonAddressStreet, PersonAddressCity, PersonAddressZip
From Person, PersonAddress
where Person.PersonKey = PersonAddress.PersonKey

--new syntax
Select PersonFirstName, PersonLastName, PersonEmail,
PersonAddressStreet, PersonAddressCity, PersonAddressZip
From Person
inner join PersonAddress
on Person.PersonKey=PersonAddress.PersonKey

--alias
Select p.Personkey,PersonFirstName, PersonLastName, PersonEmail,
PersonAddressStreet, PersonAddressCity, PersonAddressZip
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey

--multi more than two
Select * from GrantRequest

Select GrantRequestDate, PersonLastName, PersonEmail,
GrantTypeName, GrantRequestExplanation, GrantRequestAmount
From GrantRequest
inner join GrantType
on GrantRequest.GrantTypeKey=GrantType.GrantTypeKey
inner join Person
on Person.PersonKey=GrantRequest.PersonKey

--Outer joins--showing mismatchings
--returns all the records from one table 
--whether it has a match or not
--only matching records from the other table

Select Distinct GrantType.GrantTypeKey,GrantTypeName, GrantRequest.GrantTypeKey
From GrantType
Left outer Join GrantRequest
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Where GrantRequest.GrantTypeKey is null

Select Distinct GrantType.GrantTypeKey,GrantTypeName, GrantRequest.GrantTypeKey
From GrantRequest
Right outer Join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Where GrantRequest.GrantTypeKey is null

Select Distinct GrantType.GrantTypeKey,GrantTypeName, GrantRequest.GrantTypeKey
From GrantRequest
Right Join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Where GrantRequest.GrantTypeKey is null

Select Distinct GrantType.GrantTypeKey,GrantTypeName, GrantRequest.GrantTypeKey
From GrantType
full outer Join GrantRequest
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Where GrantRequest.GrantTypeKey is null