Joins

--Joins

--inner join, cross, outer joins 

Use Automart

--basic inner join with join syntax
--in the on clause you specify how the two tables
--relate
Select[FirstName],[LastName],[LicenseNumber],[VehicleMake],[VehicleYear]
From Person 
Inner Join Customer.Vehicle
On Person.Personkey=Customer.Vehicle.Personkey 
Order by LastName

--the "inner" key word is the default and not required
--though I think it is a good idea for clarity
Select[FirstName],[LastName],[LicenseNumber],[VehicleMake],[VehicleYear]
From Person 
Join Customer.Vehicle
On Person.Personkey=Customer.Vehicle.Personkey
Order by LastName

--same thing but with the tables aliased
Select p.PersonKey, [FirstName],[LastName],[LicenseNumber],[VehicleMake],[VehicleYear]
From Person p
Join Customer.Vehicle v 
On p.Personkey=v.Personkey
Order by LastName

Select p.PersonKey, [FirstName],[LastName],[LicenseNumber],[VehicleMake],[VehicleYear]
From Person p, Customer.Vehicle v
Where p.Personkey=v.PersonKey

--this results in a cross join
Select p.PersonKey, [FirstName],[LastName],[LicenseNumber],[VehicleMake],[VehicleYear]
From Person p, Customer.Vehicle v

-- two tables joined. You can join as many tables as you need
-- by repeating inner join and on
Select p.PersonKey, [FirstName],[LastName],[LicenseNumber],[VehicleMake],[VehicleYear], email
From Person as p
Inner Join Customer.Vehicle as v 
On p.Personkey=v.Personkey
Inner Join customer.RegisteredCustomer as  rc
on p.Personkey=rc.PersonKey
Order by LastName

--same thing in an older syntax
Select p.PersonKey, [FirstName],[LastName],[LicenseNumber],[VehicleMake],[VehicleYear], email
From Person p, Customer.Vehicle v, Customer.RegisteredCustomer rc
Where p.Personkey=v.PersonKey
And p.FirstName=rc.PersonKey

--explicit cross join
Select person.Personkey, LastName, FirstName, LicenseNumber, VehicleMake, VehicleYear
From Person 
Cross join Customer.Vehicle

--in an left outer join all the records in the first table
--are returned, only the matching records in the second
--table are returned. Where there is no matching record
--the result set displays a null
--outer joins are good for finding mis-matched data
--customers who never purchased anything, for instance

Select ServiceName, Employee.VehicleServiceDetail.AutoServiceID
from Customer.Autoservice
left outer join Employee.VehicleServiceDetail
on Customer.Autoservice.AutoServiceID=Employee.VehicleServiceDetail.AutoServiceID
Where Employee.VehicleServiceDetail.AutoServiceID is null


Select Lastname, rc.Personkey
From Person p
Left outer join Customer.RegisteredCustomer rc
on p.Personkey=rc.PersonKey
Where rc.PersonKey is null

--in a right join its the second table that
--returns all its records
Select LastName, rc.Personkey
From Customer.RegisteredCustomer rc
right outer join Person p
on p.Personkey=rc.PersonKey
Where rc.PersonKey is null

--full join returns all the records from both tables
--same results really as outer join
Select LastName, rc.Personkey
From Customer.RegisteredCustomer rc
full join Person p
on p.Personkey=rc.PersonKey

--just need a record with no matches
Insert into Customer.AutoService(ServiceName, ServicePrice)
Values ('Alternator Replacement', 550.00)