Joins

--Joins
--inner joins 
use Automart
Select * from Customer.RegisteredCustomer

--simple inner join using inner join syntax
--inner joins retrurn only matching records from
--the joined tables
Select LastName, Firstname, Email, CustomerPassword
from Person 
Inner Join Customer.RegisteredCustomer
on person.Personkey=Customer.RegisteredCustomer.PersonKey

--another, older way to join tables
--it uses the where clause to make the join
--it seems easier but is more dangerous and can
--result in unintended cross joins
Select LastName, Firstname, Email, CustomerPassword
From Person p, Customer.RegisteredCustomer rc
Where p.Personkey=rc.PersonKey

--intentional cross join
Select LastName, Firstname, Email, CustomerPassword
From Person p
Cross join Customer.RegisteredCustomer

--multitable inner join
 Select LastName, Firstname, Email, CustomerPassword,
 VehicleMake, VehicleYear, LicenseNumber, serviceDate
 From Person p
 inner join Customer.RegisteredCustomer rc
 on p.Personkey=rc.PersonKey
 inner join Customer.vehicle v
 on p.Personkey = v.personkey
 inner join Employee.VehicleService vs
 on v.VehicleId=vs.VehicleID

 --same join with where clause syntax
 Select LastName, Firstname, Email, CustomerPassword,
 VehicleMake, VehicleYear, LicenseNumber, serviceDate
 From Person p, Customer.RegisteredCustomer rc, Customer.Vehicle v, 
 Employee.VehicleService vs
 Where p.Personkey=rc.PersonKey
 And v.PersonKey=p.Personkey
 And v.VehicleId=vs.VehicleID
 And LastName='Anderson'

 --insert to have an unmatched value
 Insert into Customer.Autoservice( ServiceName, ServicePrice)
 values ('Replacing Upholstry',900.50)

 --outer joins return all the records from one table
 --and only matching records from the other
 --in a left join the first table named returns all its records
 --while the second one only returns matching records
 --a right outer join it is just flipped
 --the first table returns only matching records and the
 --second table returns all, matched or unmatched
 Select Distinct ServiceName, ServicePrice, vsd.AutoServiceID
 From Customer.AutoService a 
 left outer join Employee.VehicleServiceDetail vsd
 on a.AutoServiceID=vsd.AutoServiceID

 --another outer join
 Select Distinct ServiceName, ServicePrice, vsd.AutoServiceID
 From Customer.AutoService a 
 join Employee.VehicleServiceDetail vsd
 on a.AutoServiceID=vsd.AutoServiceID

 --see only the nonmatching records
 --the null in vsd.autoserviceID 
 --is only in the result set
 Select Distinct ServiceName, ServicePrice, vsd.AutoServiceID
 From Customer.AutoService a 
 left outer join Employee.VehicleServiceDetail vsd
 on a.AutoServiceID=vsd.AutoServiceID
 Where vsd.AutoServiceID is null

 --another
 Select LastName, firstName, rc.Personkey
 From Person p
 left outer join Customer.RegisteredCustomer rc
 on p.Personkey=rc.PersonKey
 where rc.PersonKey is null

 --and another
 Select Distinct LocationName, vs.LocationId
 From Customer.Location loc
 left outer join Employee.VehicleService vs
 on loc.LocationID=vs.LocationID
 Where vs.LocationID is null

 --a full join returns all the records
 --from both tables whether they are 
 --matched or not
 Select LocationName, vs.LocationId
 From Customer.Location loc
 full join Employee.VehicleService vs
 on loc.LocationID=vs.LocationID

Select LocationName, Month(ServiceDate) as [Month], Count(VehicleServiceID)  as [Count]
From Customer.Location loc
inner join Employee.VehicleService vs
on loc.LocationID=vs.LocationID
Where LocationName='Spokane'
Group by LocationName, Month(ServiceDate)
having count(VehicleServiceID) > 2