Microsoft® SQL Server® 2012 Bible

316

Part II: Building Databases and Working with Data

AND C.FlightDate = F.FlightDate

WHERE

C.LastName IS NULL

Result:

Passenger Seat

--------------------------- -------

Sam Smith 19A

The walk-up check-in query uses a LEFT OUTER JOIN and an IS NULL in the

WHERE clause to locate any passengers who are in the CheckIn table but not in the

FlightPassenger table:

-- Walk Up CheckIn

SELECT C.FirstName + ' ' + C.LastName AS Passenger, C.Seat

FROM CheckIn AS C

LEFT OUTER JOIN

FlightPassengers AS F

ON C.LastName = F.LastName

AND C.FirstName = F.FirstName

AND C.FlightCode = F.FlightCode

AND C.FlightDate = F.FlightDate

WHERE

F.LastName IS NULL

Result:

Passenger Seat

--------------------------- -------

Missy Anderson 4B

The last difference query lists any seat changes, including Jerry’s upgrade to fi rst class.

This query uses an inner join because it’s searching for passengers who both had previous

seat assignments and now are boarding with a seat assignment. The query compares the

seat columns from the FlightPassenger and CheckIn tables using a not equal compar-

ison, which fi nds any passengers with a different seat than previously assigned. Go Jerry!

-- Seat Changes

SELECT C.FirstName + ' ' + C.LastName AS Passenger, F.Seat AS

'previous seat', C.Seat AS 'final seat'

FROM CheckIn AS C

INNER JOIN FlightPassengers AS F

ON C.LastName = F.LastName

AND C.FirstName = F.FirstName

AND C.FlightCode = F.FlightCode

AND C.FlightDate = F.FlightDate

AND C.Seat <> F.Seat

WHERE F.Seat IS NOT NULL

c12.indd 316c12.indd 316 7/30/2012 4:42:42 PM7/30/2012 4:42:42 PM

http://www.it-ebooks.info