Subqueries

Use CommunityAssist

Select lastName, FirstName, City
From Person p, PersonAddress pa
Where p.PersonKey=pa.PersonKey
And not City = 'Seattle'

11. Select c.ContactTypeName
From ContactType c
Left Outer Join PersonContact pc
On c.ContactTypeKey=pc.ContactTypeKey
where pc.ContactTypeKey is null

Use MagazineSubscription

--an inner join and subquery criteria
Select Magname, SubscriptionPrice
From MagazineDetail md
Inner Join Magazine m
On m.MagID=md.MagID
Where SubscriptionPrice =
(Select MAX(SubscriptionPrice)
From MagazineDetail)

-- subquery for column definition
Select Magid, SubscriptionPrice,
(Select avg(SubscriptionPrice) from MagazineDetail) as Average
From MagazineDetail
Where SubscriptionPrice >
(Select AVG(SubscriptionPrice)
From MagazineDetail)

--subqueries using "in" for subsets
-- the logic of this is
--Return the set of customers
--whose customer ids are in the set
--of subscriptions where the subscription's
--magazine detail id is in the set of Magazine
--detail ids which have a subscription price
--equal to the smallest subscription price
Select CustfirstName, CustLastname, (Select MIn(SubscriptionPrice) from MagazineDetail) as smallest
From Customer

Where CustID in --if custID here
(Select CustID --must be custid here
from Subscription

Where magDetID in --if magdetID here
(Select MagDetID --must be magdetid here
From Magazinedetail

Where SubscriptionPrice=
(Select MIn(SubscriptionPrice)
from magazineDetail)))

--using all
--****************************************
--when you use an comparitive with a subquery
--you must use all or any
--******************************************
--all matches each value in the subquery against all
--other values. In this case the subscription price
--must be greater than or equal to all other subscription
--prices, which returns only the maximum subscription
--price
Select MagdetID, SubscriptionPrice
From MagazineDetail
Where SubscriptionPrice >= all
(Select SubscriptionPrice
From MagazineDetail)

--any says that the subcription price must be
--greater than any one of the other prices
--the effect is to return all but the smallest price
Select MagdetID, SubscriptionPrice
From MagazineDetail
Where SubscriptionPrice > any
(Select SubscriptionPrice
From MagazineDetail)

Select SubscriptTypeID, AVG(SubscriptionPrice) as Average
From MagazineDetail
Group by SubscriptTypeID

--this is a correlated subquery
--that means the subquery is dependent
--on a value from the main query
--for its completion
--it is equivalent to a recursive function
--in programming
--Note that like a self join the same table
--is aliased with two different aliases
--treating it like two tables
Select md.SubscriptTypeID, MagDetID, SubscriptionPrice
From magazineDetail md
Where SubscriptionPrice >=
(Select AVG(SubscriptionPrice)
From magazinedetail amd
where md.SubscripttypeID = amd.SubscripttypeID)

Select * from SubscriptionType

--exists returns a boolean does it exist
--in the subset or not
--a bit more efficient than in
Select Magname
From Magazine
Where Exists
(Select MagID
from MagazineDetail
Where SubscriptTypeID=5)

--another example of using exists
--to test whether a database exists
If exists
(Select name From sys.databases
where name = 'communityAssist')
Begin
print 'Yep its there'
End