Joins

Use MagazineSubscription

--inner join

Select CustLastName, CustFirstName,SubscriptionID,SubscriptionStart,SubscriptionEnd
From Customer c
Inner Join Subscription s
ON c.CustID=s.CustID

Select CustLastName, CustFirstName,SubscriptionID,SubscriptionStart,SubscriptionEnd
From Customer c, Subscription s
Where c.CustID=s.custID

--cross join
Select CustLastName, CustFirstName,SubscriptionID,SubscriptionStart,SubscriptionEnd
From Customer
Cross Join Subscription

Select CustFirstName,CustLastName,
SubscriptionID,MagName,SubscriptionPrice,
SubscriptionStart,SubscriptionEnd
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='Jordan'


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

Insert into Customer(CustLastName, CustFirstName, CustAddress, CustCity, CustState, CustZipcode, CustPhone)
Values('smith', 'joe','1000 elsewhere', 'Seattle', 'wa','98000','2065553456')

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

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


Use master

Create Database SelfJoinTest

Use SelfJoinTest

Create Table Employee
(
EmployeeID int primary key,
EmployeeLastName Nvarchar(255),
SupervisorID int
)

Insert Into Employee
Values(1,'Smith',2),
(2,'Jones',3),
(3, 'Brown',null),
(4, 'Able',2)

Select * From Employee

Select e.EmployeeLastName as "slave",
Boss.EmployeelastName as master
From Employee Boss
Inner Join Employee e
On e.SupervisorID=Boss.EmployeeID