Joins

Use MagazineSubscription

--standard ansi way to to an inner join
Select CustLastName,
CustFirstName,
SubscriptionID,
SubscriptionStart
From Customer
INNER Join Subscription --same with or without INNER
ON Customer.CustID=Subscription.CustID

--

--old way to do an inner join
Select CustLastName,
CustFirstName,
SubscriptionID,
SubscriptionStart
From Customer, Subscription
Where Customer.CustID=Subscription.CustID

--you can alias the tables to cut back on typing
Select CustLastName,
CustFirstName,
SubscriptionID,
SubscriptionStart
From Customer c --alias the customer as c
INNER Join Subscription s--same with or without INNER
ON c.CustID=s.CustID

--multiple table inner join
Select CustLastName, CustFirstName, MagName, SubscriptionStart
From Customer c
Inner Join Subscription s
ON c.CustID=s.CustID
Inner Join MagazineDetail md
On s.MagDetID=md.MagDetID
Inner Join Magazine m
On m.MagID=md.MagID
Where CustLastName='Able'

Select CustLastName, CustFirstName, MagName, SubscriptionStart
From Customer c, Subscription s, MagazineDetail md, Magazine m
Where c.CustID=s.CustID
And s.MagDetID=md.MagDetID
And md.MagID=m.MagID
And CustLastName='Able'

--if a relationship is missing you get an unintentional cross join
--also called a cartesian join or cartesian dump

Select CustLastName, SubscriptionID
From Customer
Cross Join Subscription

Select CustLastName, SubscriptionID
From Customer, Subscription

--outer joins
--left outer join
Select CustLastName, SubscriptionID
From Customer c
Left Outer Join Subscription s
On c.CustID=s.CustID
Where SubscriptionID is null

--right outer join
Select CustLastName, SubscriptionID
From Subscription s
Right Outer Join Customer c
On c.CustID=s.CustID
Where SubscriptionID is null

--Select Magname, s.SubscriptionID
--From Magazine m
--inner join MagazineDetail md
--On m.MagID=md.MagID
--Inner Join Subscription s
--On s.MagDetID=md.MagDetID
--Left Outer Join Subscription s2
--on s2.MagDetID=md.MagDetID
--Where s2.SubscriptionID is null

Select MagName, Count(s.subscriptionID) as Orders
From Magazine m
Inner Join MagazineDetail md
On m.MagID=md.MagID
Left Outer Join Subscription s
On md.magDetID=s.MagDetID
Group by magName
Having count(s.subscriptionID) < 1


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

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