Queries classified

I know this is annowing, but i couldn't figure out how to linc a document into my blog entry. So here it is.

Simple

Table: Activity
SELECT * FROM Activity WHERE campID='cid'

SELECT MAX(activityID) AS m FROM Activity

Table: Amenity
SELECT * FROM Amenity WHERE groundID=gid

SELECT * FROM Amenity WHERE groundID=gid ORDER BY amenityName

Table: Announcement
SELECT * FROM Announcement
WHERE campID <= 0 AND
(annBeginDate <= GETDATE() OR annBeginDate = '' OR annBeginDate IS NULL) AND
(annEndDate >= GETDATE() OR annEndDate = '' OR annEndDate IS NULL)
ORDER BY announceID DESC

SELECT * FROM Announcement
WHERE campID=cid AND
(annBeginDate <= GETDATE() OR annBeginDate = '' OR annBeginDate IS NULL) AND
(annEndDate >= GETDATE() OR annEndDate = '' OR annEndDate IS NULL)
ORDER BY announceID DESC

SELECT announceID, announceTitle, announceText,
CAST(MONTH(annBeginDate) AS varchar) + '/' +
CAST(DAY(annBeginDate) AS varchar) + '/' +
CAST(YEAR(annBeginDate) AS varchar) annBeginDate,
CAST(MONTH(annEndDate) AS varchar) + '/' +
CAST(DAY(annEndDate) AS varchar) + '/' +
CAST(YEAR(annEndDate) AS varchar) annEndDate
FROM Announcement
WHERE campID='cid'

Table: Camp
SELECT * FROM Camp ORDER BY name

SELECT * FROM Camp WHERE campID='cid'

SELECT COUNT(*) FROM Camp

SELECT allowYWSaturdays FROM Camp WHERE campID=cid

SELECT campID, name FROM Camp

SELECT campID, name FROM Camp ORDER BY name

SELECT campID, name FROM Camp WHERE campID='cid'

SELECT descrip, pic FROM Camp WHERE campID=cid

SELECT directionmap FROM Camp WHERE campID=cid

SELECT mappic FROM Camp WHERE campID=cid

SELECT name FROM Camp WHERE campID='cid'

SELECT name, contactEmail, campEmail FROM Camp WHERE campID=cid

SELECT firstScheduled, lastScheduled FROM Camp WHERE campID=cid

SELECT nextfirstScheduled, nextlastScheduled FROM Camp WHERE campID=cid

SELECT COUNT(name),
(SELECT COUNT(*) FROM Reservation WHERE confirmed<>'N') AS Confirmed,
(SELECT COUNT(*) FROM Reservation WHERE confirmed='N') AS Unconfirmed,
(SELECT COUNT(*) FROM Reservation) AS Total,
(SELECT COUNT(*) FROM UnavailableDate) AS Unavailable
FROM Camp


Complex
SELECT name, acceptReservations,
(SELECT COUNT(*) FROM Reservation WHERE Reservation.campID=c.campID AND confirmed<>'N') AS Confirmed,
(SELECT COUNT(*) FROM Reservation WHERE Reservation.campID=c.campID AND confirmed='N') AS Unconfirmed,
(SELECT COUNT(*) FROM Reservation WHERE Reservation.campID=c.campID) AS Total,
(SELECT COUNT(*) FROM UnavailableDate WHERE UnavailableDate.campID=c.campID) AS Unavailable,
(SELECT COUNT(*) FROM Campsite s, Campground g WHERE s.groundID=g.groundID AND g.campID=c.campID) AS Sites,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (date0)) As Day0,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (date1)) As Day1,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (date2)) As Day2,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (date3)) As Day3,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc, ReservationDate rd
WHERE r.reservID=rfc.reservID AND rfc.reservID=rd.reservID AND
r.campID=c.campID AND rd.reservDate IN (date4)) As Day4,
(SELECT COUNT(*) FROM Reservation r, ReservationForCampsite rfc
WHERE r.reservID=rfc.reservID AND r.startDate=r.endDate AND
r.campID=c.campID AND r.startDate IN (date5)) AS Day5
FROM Camp c
ORDER BY name

SELECT acceptReservations, nextacceptReservations,
CAST(MONTH(firstScheduled) AS VARCHAR) + '/' +
CAST(DAY(firstScheduled) AS VARCHAR) + '/' +
CAST(YEAR(firstScheduled) AS VARCHAR) AS firstScheduled,
CAST(MONTH(lastScheduled) AS VARCHAR) + '/' +
CAST(DAY(lastScheduled) AS VARCHAR) + '/' +
CAST(YEAR(lastScheduled) AS VARCHAR) AS lastScheduled,
allowYWSaturdays,
CAST(MONTH(nextfirstScheduled) AS VARCHAR) + '/' +
CAST(DAY(nextfirstScheduled) AS VARCHAR) + '/' +
CAST(YEAR(nextfirstScheduled) AS VARCHAR) AS nextfirstScheduled,
CAST(MONTH(nextlastScheduled) AS VARCHAR) + '/' +
CAST(DAY(nextlastScheduled) AS VARCHAR) + '/' +
CAST(YEAR(nextlastScheduled) AS VARCHAR) AS nextlastScheduled,
currentSeason, nextSeason
FROM Camp
WHERE campID=cid

SELECT acceptReservations, nextacceptReservations,
CAST(MONTH(firstScheduled) AS VARCHAR) + '/' +
CAST(DAY(firstScheduled) AS VARCHAR) + '/' +
CAST(YEAR(firstScheduled) AS VARCHAR) AS firstScheduled,
CAST(MONTH(lastScheduled) AS VARCHAR) + '/' +
CAST(DAY(lastScheduled) AS VARCHAR) + '/' +
CAST(YEAR(lastScheduled) AS VARCHAR) AS lastScheduled,
allowYWSaturdays,
CAST(MONTH(nextfirstScheduled) AS VARCHAR) + '/' +
CAST(DAY(nextfirstScheduled) AS VARCHAR) + '/' +
CAST(YEAR(nextfirstScheduled) AS VARCHAR) AS nextfirstScheduled,
CAST(MONTH(nextlastScheduled) AS VARCHAR) + '/' +
CAST(DAY(nextlastScheduled) AS VARCHAR) + '/' +
CAST(YEAR(nextlastScheduled) AS VARCHAR) AS nextlastScheduled,
allowYWSaturdays
FROM Camp
WHERE campID=cid

Table: Campground
SELECT * FROM Campground WHERE campID=cid ORDER BY groundName

SELECT * FROM Campground WHERE groundID=gid ORDER BY groundName

SELECT MAX(groundID) AS m FROM Campground

SELECT MIN(groundID) FROM Campground WHERE campID='cid'

SELECT groundID, groundName FROM Campground WHERE campID=cid ORDER BY groundName

Table: CampPicture
SELECT * FROM CampPicture WHERE campID='cid'

SELECT MAX(picID) AS m FROM CampPicture

SELECT picloc FROM CampPicture WHERE campID=cid

Table: CampRule
SELECT * FROM CampRule WHERE catID=cid

Table: CampRuleCategory
SELECT * FROM CampRuleCategory WHERE campID='cid'

SELECT MIN(catID) FROM CampRuleCategory WHERE campID='cid'

Table: Campsite
SELECT * FROM Campsite WHERE groundID='gid' ORDER BY siteName

SELECT COUNT(*) FROM Campsite WHERE groundID='gid'

SELECT siteCapacity FROM Campsite WHERE groundID=gid

SELECT siteCapacity FROM Campsite WHERE siteID='sid'

Table: Codes
SELECT code, description FROM Codes WHERE ctype='Activity' ORDER BY pos

SELECT code, description FROM Codes WHERE ctype='Priority' ORDER BY pos

SELECT pos FROM Codes WHERE ctype='Tracking' AND code='GA'

Table: Direction
SELECT * FROM Direction

SELECT * FROM Direction WHERE campID='cid'

Table: Facility
SELECT * FROM Facility WHERE campID='cid'

SELECT MAX(facilityID) AS m FROM Facility

Table: FAQ
SELECT * FROM FAQ ORDER BY pos

Table: Form
SELECT * FROM Form

Table: GeneralSite
SELECT * FROM GeneralSite

SELECT about, aboutpic, reserveInfo FROM GeneralSite

SELECT map FROM GeneralSite

Table: Glossary
SELECT * FROM Glossary ORDER BY term

Table: PrioritySchedule or NextPrioritySchedule
SELECT MAX(priorityallowed) FROM PrioritySchedule WHERE asOfDate < GETDATE()

SELECT MAX(priorityallowed) FROM NextPrioritySchedule WHERE asOfDate < GETDATE()

-- n is an integer between 1 and 6 inclusive
SELECT asOfDate FROM PrioritySchedule
WHERE priorityallowed='n' AND DATEPART(month,asOfDate) > 3 AND DATEPART(year, asOfDate) <= DATEPART(year,GETDATE())

SELECT asOfDate FROM NextPrioritySchedule
WHERE priorityallowed='n' AND DATEPART(month,asOfDate) > 3 AND DATEPART(year, asOfDate) <= DATEPART(year,GETDATE())

SELECT asOfDate FROM PrioritySchedule WHERE priorityAllowed=n

SELECT asOfDate FROM NextPrioritySchedule WHERE priorityAllowed=n

Table: Reservation
SELECT * FROM Reservation WHERE reservID=rid

SELECT MAX(reservID) FROM Reservation

SELECT contactEmail FROM Reservation WHERE reservID=rid

SELECT DISTINCT endDate FROM Reservation WHERE campID=id ORDER BY endDate

Table: ReservationDate
SELECT * FROM ReservationDate WHERE reservDate='date' AND reservID='rid'

SELECT reservID FROM ReservationDate WHERE reservDate='date'

SELECT reservDate FROM ReservationDate WHERE reservID='rid'

Table: ReservationForCampsite
SELECT * FROM ReservationForCampsite WHERE siteID='sid' AND reservID='rid'

Table: RuleCategory
SELECT * FROM RuleCategory

SELECT MIN(catID) FROM RuleCategory

Table: RuleTable
SELECT * FROM RuleTable

SELECT * FROM RuleTable WHERE catID='cid'

Table: Stake
SELECT stakeID, stakeName FROM Stake WHERE stakeID > 1 ORDER BY stakeName

SELECT stakeName FROM Stake WHERE stakeID=sid

Table: UnavailableDate
SELECT unavailDate FROM UnavailableDate WHERE campID=cid

SELECT unavailDate FROM UnavailableDate WHERE campID=cid ORDER BY unavailDate

Table: UserCamp
SELECT * FROM UserCamp WHERE userID='uid'

SELECT campID FROM UserCamp WHERE userID='uid'

Table: UserTable
SELECT * FROM UserTable WHERE userID='uid' AND password='password'

SELECT * FROM UserTable WHERE userID='uid'

SELECT userID, name FROM UserTable

Table: Ward
SELECT wardID, wardName, unittype FROM Ward WHERE stakeID='sid' ORDER BY wardName

SELECT wardName FROM Ward WHERE wardID=wid

Tables: AgentStake joined with others
SELECT AgentStake.*, Stake.stakeName
FROM AgentStake, Stake
WHERE campID=cid AND AgentStake.stakeID=Stake.stakeID

SELECT stakeName, c.name AS name, g.stakeID AS asi, s.stakeID AS si
FROM Stake s, Camp c, AssignedTo a, AgentStake g
WHERE s.stakeID=a.stakeID AND c.campID=a.campid AND g.campID=c.CampID
ORDER BY stakeName, Camp.name

SELECT stakeName, c.name AS name, g.stakeID AS asi, s.stakeID AS si
FROM Stake s, Camp c, AssignedTo a, AgentStake g
WHERE s.stakeID=a.stakeID AND c.campID=a.campid AND g.campID=c.CampID
ORDER BY c.name, stakeName

SELECT startDate, endDate, numPeople, groupType,
ward, reservedBy, r.campID,
r.contactPhone, contactAddress,
r.contactEmail, specialRequests,
managerComments, confirmed, totCost, c.name as propertyName,
managerName, c.contactPhone AS manPhone,
c.contactEmail as manEmail,
g.name AS spName, street, city, state, zip,
s.stakeName AS ssname, a.stakeName AS asname
FROM Reservation r, Camp c, AgentStake g, Stake s, Stake a
WHERE r.campID=c.campID AND c.campID=g.CampID AND
s.stakeID=stake AND a.stakeID=g.stakeID AND reservID=rid

Tables: AssignedTo and {Stake or Ward}
SELECT s.stakeID, stakeName
FROM AssignedTo a, Stake s
WHERE a.stakeID=s.stakeID AND a.campID='cid'
ORDER BY stakeName

SELECT wardID, wardName, unitType
FROM AssignedTo a, Ward w
WHERE w.stakeID=sid AND w.stakeID=a.stakeID AND a.campID=cid +
ORDER BY wardName

Tables: Camp, Campground, Campsite, and ReservationForCampsite
SELECT s.siteID, s.siteName, s.siteCapacity, g.groundName, r.reservID
FROM Campsite s
INNER JOIN Campground g ON (s.groundID=g.groundID)
INNER JOIN Camp c ON (g.campID=c.campID)
LEFT JOIN ReservationForCampsite r ON (r.reservID=" + editMode AND r.siteID=s.siteID)
WHERE c.campID=cid AND c.campID=g.campID AND g.groundID=s.groundID
ORDER BY g.groundName, s.siteName

Tables: Camp and Reservation
SELECT r.reservID, r.startdate, r.enddate, r.grouptype, r.stake, r.ward,
r.contactPhone, r.contactAddress, r.contactEmail, r.specialRequests,
r.managerComments, r.confirmed, r.status, r.numpeople, r.reservedBy,
r.dateCreated, c.name, c.campID
FROM Reservation r, Camp c
WHERE r.campID=cid AND r.campID=c.campID
ORDER BY c.name, r.reservID

SELECT r.reservID, r.startdate, r.enddate, r.grouptype, r.stake, r.ward,
r.contactPhone, r.contactAddress, r.contactEmail, r.specialRequests,
r.managerComments, r.confirmed, r.status, r.numpeople, r.reservedBy,
r.dateCreated, c.name, c.campID
FROM Reservation r, Camp c
WHERE r.campID=c.campID
ORDER BY c.name, r.reservID

Tables: Campground and Campsite
SELECT c.groundID, c.groundName, s.siteID, s.siteName
FROM Campground c INNER JOIN Campsite s ON (c.groundid = s.groundID)
WHERE c.campID=cid
ORDER BY c.groundName, s.siteName

Tables: Campground, Campsite, and ReservationForCampsite
SELECT *
FROM ReservationForCampsite rfc, Campsite cs, Campground cg
WHERE rfc.reservID='rid' AND rfc.siteID=cs.siteID AND cs.groundID=cg.groundID
ORDER BY cg.groundName, cs.siteName

Tables: CampRule and CampRuleCategory
SELECT crc.category, cr.text
FROM CampRule cr LEFT JOIN CampRuleCategory crc ON (cr.catID=crc.catID)
WHERE crc.campID=cid
ORDER BY crc.campID, crc.category

SELECT DISTINCT category, crc.catID
FROM CampRuleCategory crc, CampRule cr
WHERE crc.catID=cr.catID AND campID=cid

Tables: Campsite and ReservationForCampsite
SELECT s.sitename FROM ReservationForCampsite r, Campsite s
WHERE r.reservID='rid' AND r.siteID=s.siteID

Tables: Reservation and Stake
SELECT * FROM Reservation r, Stake WHERE reservID='rid' AND r.stake=stake.stakeID

Tables: Reservation and ReservationForCampsite
SELECT r.reservid, r.reservedBy, r.stake, r.ward, r.groupType,
CAST(r.startDate AS DateTime) AS arrival,
CAST(r.endDate AS DateTime) AS departure,
(SELECT stakeName FROM Stake s
WHERE s.stakeID=r.stake) AS stakeName,
(SELECT wardName FROM Ward w
WHERE CAST(w.wardID AS NVARCHAR)=r.ward) AS wardName
FROM Reservation r
INNER JOIN ReservationForCampsite rfc ON (r.reservid=rfc.reservid)
WHERE ((CAST(r.startDate AS DateTime) >= 'firstDate' AND
CAST(r.startDate AS DateTime) <= 'lastDate') OR
(CAST(r.endDate AS DateTime) >= 'firstDate' AND
CAST(r.endDate AS DateTime) <= 'lastDate')) AND r.campID=cid AND rfc.siteID=sid
ORDER BY departure

Tables: Larger Joins
SELECT DISTINCT
rd.reservDate, g1.groundID,
(SELECT COUNT(*)
FROM Campsite
WHERE Campsite.groundID = g1.groundID
GROUP BY Campsite.groundID) -
(SELECT COUNT(*)
FROM ReservationForCampsite rfc2
INNER JOIN ReservationDate rd2 ON (rfc2.reservID=rd2.reservID)
INNER JOIN Campsite s2 ON (rfc2.siteID=s2.siteID)
WHERE rd2.reservDate=rd.reservDate AND g1.groundID=s2.groundID
) AS sitesOpen,
(SELECT COUNT(r5.groupType)
FROM Campground cg5
INNER JOIN AdjacentCampground a ON (cg5.groundID=a.groundID)
INNER JOIN Campsite c5 ON (c5.groundID=a.adjacentTo)
INNER JOIN ReservationForCampsite rfc5 ON (c5.siteID=rfc5.siteID)
INNER JOIN Reservation r5 ON (r5.reservID=rfc5.reservID)
INNER JOIN Reservationdate rd5 ON (r5.reservID = rd5.reservID)
WHERE cg5.campID=g1.campID AND rd5.reservDate=rd.reservDate
AND cg5.groundID=g1.groundID AND r5.groupType='YW'
) AS countAdjacentYW,
(SELECT COUNT(r5.groupType)
FROM Campground cg5
INNER JOIN AdjacentCampground a ON (cg5.groundID=a.groundID)
INNER JOIN Campsite c5 ON (c5.groundID=a.adjacentTo)
INNER JOIN ReservationForCampsite rfc5 ON (c5.siteID=rfc5.siteID)
INNER JOIN Reservation r5 ON (r5.reservID=rfc5.reservID)
INNER JOIN Reservationdate rd5 ON (r5.reservID = rd5.reservID)
WHERE cg5.campID=g1.campID AND rd5.reservDate=rd.reservDate
AND cg5.groundID=g1.groundID AND r5.groupType<>'YW'
) AS countAdjacentNonYW
FROM Campground g1
INNER JOIN Reservation r ON (r.campID=g1.campID)
INNER JOIN ReservationForCampsite rfc ON (r.reservID=rfc.reservID)
INNER JOIN Campsite s ON (s.groundID=g1.groundID)
INNER JOIN ReservationDate rd ON (rd.reservID=r.reservID)
WHERE g1.campID=cid
UNION
SELECT DISTINCT
r.startdate, g1.groundID,
(SELECT COUNT(*)
FROM Campsite
WHERE Campsite.groundID = g1.groundID
GROUP BY Campsite.groundID) -
(SELECT COUNT(*)
FROM ReservationForCampsite rfc2
INNER JOIN ReservationDate rd2 ON (rfc2.reservID=rd2.reservID)
INNER JOIN Campsite s2 ON (rfc2.siteID=s2.siteID)
WHERE rd2.reservDate=r.startdate AND g1.groundID=s2.groundID
) AS sitesOpen,
(SELECT COUNT(r5.groupType)
FROM Campground cg5
INNER JOIN AdjacentCampground a ON (cg5.groundID=a.groundID)
INNER JOIN Campsite c5 ON (c5.groundID=a.adjacentTo)
INNER JOIN ReservationForCampsite rfc5 ON (c5.siteID=rfc5.siteID)
INNER JOIN Reservation r5 ON (r5.reservID=rfc5.reservID)
INNER JOIN Reservationdate rd5 ON (r5.reservID = rd5.reservID)
WHERE cg5.campID=g1.campID AND rd5.reservDate=r.startdate
AND cg5.groundID=g1.groundID AND r5.groupType='YW'
) AS countAdjacentYW,
(SELECT COUNT(r5.groupType)
FROM Campground cg5
INNER JOIN AdjacentCampground a ON (cg5.groundID=a.groundID)
INNER JOIN Campsite c5 ON (c5.groundID=a.adjacentTo)
INNER JOIN ReservationForCampsite rfc5 ON (c5.siteID=rfc5.siteID)
INNER JOIN Reservation r5 ON (r5.reservID=rfc5.reservID)
INNER JOIN Reservationdate rd5 ON (r5.reservID = rd5.reservID)
WHERE cg5.campID=g1.campID AND rd5.reservDate=r.startdate
AND cg5.groundID=g1.groundID AND r5.groupType<>'YW'
) AS countAdjacentNonYW
FROM Campground g1
INNER JOIN Reservation r ON (r.campID=g1.campID)
INNER JOIN ReservationForCampsite rfc ON (r.reservID=rfc.reservID)
INNER JOIN Campsite s ON (s.groundID=g1.groundID)
WHERE g1.campID=cid


-- NOTE that the next two queries execute one after another in a loop for
-- all days in a chosen date range (could be day, week, month, year).
SELECT *
FROM ReservationForCampsite rfc, Campsite c, Reservation r, ReservationDate rd
WHERE rfc.siteID=c.siteID AND rfc.reservID=r.reservID AND c.siteID='sid'
AND rd.reservID=r.reservID AND rd.reservDate='date'

SELECT DISTINCT r.grouptype
FROM Campsite c
INNER JOIN Campground g ON (c.groundID=g.groundID)
INNER JOIN AdjacentCampground a ON (g.groundID=a.groundID)
INNER JOIN Campsite c2 ON (c2.groundID=a.adjacentTo)
INNER JOIN ReservationForCampsite rfc ON (c2.siteID=rfc.siteID)
INNER JOIN reservation r ON (rfc.reservID=r.reservID)
INNER JOIN reservationdate rd ON (r.reservID=rd.reservID)
WHERE rd.reservDate='date' AND r.campID=cid AND c.siteID=sid
-- This second part picks up Saturday afternoon/evening reservations.
UNION
SELECT DISTINCT r.grouptype
FROM Campsite c
INNER JOIN Campground g ON (c.groundID=g.groundID)
INNER JOIN AdjacentCampground a ON (g.groundID=a.groundID)
INNER JOIN Campsite c2 ON (c2.groundID=a.adjacentTo)
INNER JOIN ReservationForCampsite rfc ON (c2.siteID=rfc.siteID)
INNER JOIN reservation r ON (rfc.reservID=r.reservID)
WHERE r.startdate='date' AND r.campID=cid AND c.siteID=sid