/************************************
this script provides samples for 
Various types of Joins between tables
***************************************/

Use MagazineSubscription
--Inner Joins

--basic inner join two tables
Select CustLastName, CustFirstName , subscriptionID, SubscriptionStart
From Customer c
Inner join Subscription s
On c.CustID=s.custID

-- Join and inner join are equivalent, but inner join is preferable
--because it is more descriptive of what you are doing

Select CustLastName, CustFirstName ,subscriptionID, SubscriptionStart
From Customer c
join Subscription s
On c.CustID=s.custID
where custLastName='Able'

/*You will also be expected to know the equi-join 
equivalents to the join statements especially since
some older DBMSs don't support the Join syntax. You should
use the Join syntax where possible because it is 
clearer about what you are actually doing */

--equi-join equivalent

Select CustLastName, CustFirstName ,subscriptionID, SubscriptionStart
From Customer c, Subscription s
Where c.CustID=s.custID
And custLastName='Able'

--Multiple inner joins. this would answer the question
--What Magazines has Tina Able subscribed to

Select CustLastName, MagName, s.SubscriptionID,
SubscriptionStart, SubscriptionEnd
From Customer c
Inner Join Subscription s
On c.CustID=s.custID
Inner Join MagazineDetail md
On md.magDetID = s.MagDetID
Inner Join Magazine m
On m.MagID=md.MagID
Where CustLastName='Able'

--equi-Join equivalent

Select CustLastName, MagName, s.SubscriptionID,
SubscriptionStart, SubscriptionEnd
From Customer c, MagazineDetail md, Subscription s, Magazine m
Where c.custID=s.custID
And md.MagDetID=s.MagDetID
AND m.MagID=md.MagID
And CustLastName='Able'

--Inner Join with Aggregate Functions. This query
--answers the question How much has each customer
--paid for their subscriptions

Select CustLastName, Sum(SubscriptionPrice) as "Total Paid"
From Customer c
Inner Join Subscription s
On c.custID=s.custID
Inner Join MagazineDetail md
on md.magDetID=s.magdetID
Group by CustLastName
Order by CustLastName

--equi Join equivalent
Select CustLastName, Sum(SubscriptionPrice) as "Total Paid"
From Customer c, Subscription s, MagazineDetail md
Where c.custID=s.custID
And md.MagDetID=s.MagDetID
Group by CustLastName
Order by CustLastName

--Cross Join
Select CustLastName, s.SubscriptionID
From Customer 
Cross Join Subscription s

--equijoin equivalent

Select CustLastName, s.SubscriptionID
From Customer, Subscription s

--Outer Joins
--Left outer join Returns all customers and 
--any matching Magazines. If no
--subscriptions then Null
Select CustLastName, SubscriptionID
From Customer c
Left Outer Join Subscription s
On c.CustID=s.custID

--a right outer join is exactly the same just reverse the order of the tables
Select CustLastName, SubscriptionID
From Subscription s
right Outer Join customer c
On c.CustID=s.custID


--SQL server 2005 no longer supports the non
--ans *= and =* syntax

--To locate all customers that don't have a subscription
Select CustLastName, SubscriptionID
From Customer c
Left Outer Join Subscription s
On c.CustID=s.custID
Where s.SubscriptionID is Null

--To locate all magazines that have never been
--subscribed to 
--first I see what is subscribe to
Select Distinct MagName
From Magazine m
Inner Join MagazineDetail md
On m.MagID=md.MagID
Inner Join Subscription s
On md.magdetid=s.magdetid

--then what is not. You might be able
--to find a more elegant solution
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', 2)
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