Nested queries difficulty

Hi!
I'm a SQL beginner struggling with some SQL textbook examples, and i'm stuck at one exercise. :eek:
Can anyone give me a hint??

Ok, here it goes:
I'm trying to simulate a airport booking system, where CUSTOMER makes a RESERVATION to a FLIGHT. The FLIGHT have a ROUTE, a AIRCRAFT, and some CABINSTAFF. Plus some other minor attribures..

The question i'm trying to answer now is "how many seats are there left on all flights having a specific route?". I can get how many seats the airplanes have with this:

SELECt SEATS
FROM FLIGHT FL, AIRCRAFT AC, ROUTE R
WHERE FL.FNUMBER = R.FLIGHTNUMBER
AND FL.AIRCRAFT = AC.NAME
AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
AND "DATE" = '2005-03-01';

It will return:
100
200
(there are two flights that match the selected route and date, one airplane have 100 seats, the other one 200).

I then find out how many customers that are booked on those flights:

SELECT COUNT(RESERVATION_NO)
FROM FLIGHT F, AIRCRAFT A, ROUTE R, RESERVATION RN, RESERVES RS
WHERE RN.RESERVATION_NO = RS.RESERVATION_NO
AND RN.FNUMBER = R.FLIGHTNUMBER
AND F.FNUMBER = RN.FNUMBER
AND F.AIRCRAFT = A.NAME
AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
AND FDATE = '2005-03-01'
GROUP BY RN.RESERVATION_NO

It will correctly return:
1
2

Now i want to substract the second query from the first to get the number of seats left in those flights:

SELECT FLIGHTNUMBER, SEATS - (SELECT COUNT(RESERVATION_NO)
FROM FLIGHT F, AIRCRAFT A, ROUTE R, RESERVATION RN, RESERVES RS
WHERE RN.RESERVATION_NO = RS.RESERVATION_NO
AND RN.FNUMBER = R.FLIGHTNUMBER
AND F.FNUMBER = RN.FNUMBER
AND F.AIRCRAFT = A.NAME
AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
AND FDATE = '2005-03-01'
GROUP BY RN.RESERVATION_NO)
FROM FLIGHT FL, AIRCRAFT AC, ROUTE R
WHERE FL.FNUMBER = R.FLIGHTNUMBER
AND FL.AIRCRAFT = AC.NAME
AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
AND "DATE" = '2005-03-01';

But that does'nt work since you can't substract a set, only a single row, right?? So how do i fix this??
The answer should be:
219
98

Thanks!Without getting into the details of your query, you need to correlate the subquery to the main query something like this:

SELECT FL.FLIGHTNUMBER, SEATS - (SELECT COUNT(RESERVATION_NO)
FROM FLIGHT F, ...
WHERE ...
AND F.FLIGHTNUMBER = FL.FLIGHTNUMBER)
FROM FLIGHT FL, ...;

Need Tutorial for SQL Server Reporting Tool

Hi

I need good tutorial for Sql Server Reporting Service .

I m a beginner ......

Regards

Niroshan

Free training CD ROMs

http://www.appdev.com/promo/freecd.asp?PC=SN00031

Videos

http://content.ricksql.com/content/

Google

http://www.google.ca/search?q=reporting+services+filetype%3Adoc+rendering&hl=en

http://www.google.ca/search?hl=en&q=reporting+services+filetype%3Adoc+handson&meta=

cheers,

Andrew

|||

Check out these:

TechNet Webcast: Introduction to Microsoft SQL Server 2005 Reporting Services (Level 200)

Monday, October 23, 2006

11:30 A.M.–12:30 P.M. Pacific Time

Tune in to learn about the many new and exciting features that have been added to Reporting Services since Microsoft SQL Server 2000.

and these links

SQL Server 2005 Webcasts: http://www.microsoft.com/events/series/msdnsqlserver2005.mspx or http://www.microsoft.com/events/series/technetsqlserver2005.mspx

SQL Server 2005 Virtual Labs: http://msdn.microsoft.com/virtuallabs/sql/