Nested SQL problem

Hi,

Although I am quite familiar with MS Access-grade SQL, I am struggling a bit with proper grown up SQL Server. My usual approach to counting things in Access is to first create a query with the conditions on the data, then use this as the basis of a second query that does the actual counting of the presorted data. I believe the way to do this in SQL server is to use a nested query. I want to generate the top 10 highest counts for each pesticide detected (detection is level>0) for a client between two dates.

Currently I am using

<code>

SELECT top 10 Count(Pesticide) AS CountOfPesticide, Pesticide FROM (SELECT tblData.Pesticide, tblData.Level, tblData.Clast, tblData.Client FROM tblData WHERE (((tblData.Day>@.sdate) AND (tblData.Day<@.edate))) and (tbldata.level>0) and (tbldata.clast=@.clast) and (tbldata.client=@.client)) as monkeyboy GROUP BY Pesticide ORDER BY Count(Pesticide) ASC"

</code>

The results that the above SQL turns out though are not reliable. For example, if I set the dates to now and 14 days ago, it produces higher counts for some pesticides then if I set the dates to now and 30 days ago. Any pointers or general advice about nested sql is gratefully accepted!

thanks

Mike

I had to split out your SQL statement so that I could understand the logic:

SELECT TOP 10

   Count(Pesticide) AS CountOfPesticide,

   Pesticide

FROM

   (

      SELECT

         tblData.Pesticide,

         tblData.Level,

         tblData.Clast,

         tblData.Client

      FROM

         tblData

      WHERE

         (

            (

               (tblData.Day>@.sdate) AND

               (tblData.Day<@.edate)

            )

         ) AND

         (tbldata.level>0) AND

         (tbldata.clast=@.clast) AND

         (tbldata.client=@.client)

   ) AS monkeyboy

GROUP BY

   Pesticide

ORDER BY

   Count(Pesticide) ASC

I think it could be simplified to this (which does not change the logicat all but removes extraneous punctuation and columns that are notneeded and only confuse issues) :

SELECT TOP 10

   Count(Pesticide) AS CountOfPesticide,

   Pesticide

FROM

   (

      SELECT

         tblData.Pesticide

      FROM

         tblData

      WHERE

         tblData.Day>@.sdate AND

         tblData.Day<@.edate AND

         tbldata.level>0 AND

        tbldata.clast=@.clast AND

        tbldata.client=@.client

   ) AS monkeyboy

GROUP BY

   Pesticide

ORDER BY

   1 ASC

And unless I am missing something, I think it could be further simplified to this:

SELECT TOP 10

   Count(Pesticide) AS CountOfPesticide,

   Pesticide

FROM

   tblData

WHERE

   tblData.Day>@.sdate AND

   tblData.Day<@.edate AND

   tbldata.level>0 AND

  tbldata.clast=@.clast AND

  tbldata.client=@.client

GROUP BY

   Pesticide

ORDER BY

   1 ASC

The nested query is usually only needed if you need to perform a GROUP BY within in.
If the missing GROUP BY is not the problem, then my next guess thatthere is something unusual going on with the data types. Thequestions I have are:
What is the data type of tblData.Day?|||

Thanks Terri,

This is very helpful and I'll go and play with the new simplified SQL.

The tblData.Day and @.sdate (startdate) and @.edate (enddate) are all dates.

Since I am in the UK, I have had to manually convert the parameter dates to strings in the "MM/DD/YYYY" format so that SQL server behaves itself (I believe it works in US-style date formats or ISO ones only). I have been caught out before with date-related conversion problems and so have taken exeptional care here to make sure I haven't been caught out again.

thanks for your help

Mike

|||Try using YYYYMMDD format instead to avoid those sorts of problems(this format is called ISO). You would still declare @.sdate and@.edate as one of the datetime data types.

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, ...;