Joins

Select * From sys.Databases
Use CommunityAssist
Select name from sys.procedures

--joins
-- cross join
Select PersonLastName, Street, City from Person, PersonAddress

Select PersonLastName, Street, City from Person
Cross Join PersonAddress

--inner joins
Select PersonLastName, ContactInfo, ContactType.ContactTypeKey,
[ContactTypename]
From Person, PersonContact, ContactType
Where Person.PersonKey=PersonContact.Personkey
And ContactType.ContactTypeKey=PersonContact.ContactTypeKey

Select PersonLastName, ct.ContactTypeKey, ContactInfo, ContactTypeName
From Person as p
inner join PersonContact as pc
On p.PersonKey=pc.PersonKey
Inner join ContactType ct
on pc.ContactTypeKey=ct.ContactTypeKey

Select Distinct PersonLastname,  donationDate, sum(DonationAmount)
from Person p
join PersonContact pc
on p. PersonKey = pc.PersonKey
join donation d
on d.PersonKey=p.PersonKey
Group by PersonLastname,  donationDate


--outer Joins
Select Distinct ServiceName, sg.ServiceKey from CommunityService cs
left outer join ServiceGrant sg
on cs.serviceKey=sg.ServiceKey
Where sg.ServiceKey is null

Select Distinct ServiceName, sg.ServiceKey from CommunityService cs
full outer join ServiceGrant sg
on cs.serviceKey=sg.ServiceKey
Where sg.ServiceKey is null

Select Distinct PersonLastName, ContactInfo, ContactTypeName
From Person p
Inner Join PersonContact pc
on p.PersonKey=pc.PersonKey
right join ContactType ct
On Ct.ContactTypeKey=pc.ContactTypeKey
Where ContactInfo is null