Nested SQL Query

[springboardjg]

Hi

I have a table with the following columns: Site_Serial, HSI_Year, HSI_Month and Weekly_Average. The latter three columns contain number fields rather than text.

What I need to do is return the Weekly Averages for a certain month, the most recent being April 2011, which is HSI_Year=2011 and HSI_Month=4.

I need to add a nested query to the following SQL query:

SELECT Site_Serial, HSI_Year, HSI_Month, Weekly_Average
FROM tbl_SiteSerialsAndWeeklyAverages
WHERE HSI_Year=2011 AND HSI_Month=4

The reason I need a nested query is that I only want to return a row for April 2011 where there was also a weekly average for March 2011 (HSI_Year=2011 and HSI_Month=3) for the same site serial. I know I may need to use IS NOT NULL but I'm not sure how to nest this into my current query.



Any help would be greatly appreciated, either the exact query or just help with the syntax.


Thanks

James

[alansidman]

Using the SQL statement you have provided, what results do you get? It appears that you should only get results for April 2011.

I only want to return a row for April 2011 where there was also a weekly average for March 2011 (HSI_Year=2011 and HSI_Month=3) for the same site serial.

This has me confused.

Alan

[springboardjg]

Hi Alan

Yes with the current SQL query I get all the results for April 2011, which is not quite what I want.

If you look at the table below then the SQL query would return 'ARMG0030001' and 'BDFD0020003' and 'DRBY0320001' for April 2011, but I don't want 'BDFD0020003' for April 2011 because it has a null value for March 2011. Similarly I don't want 'DRBY0320001' for April 2011 either because it has a null value for April 2011.

 Site_Serial | HSI_Year | HSI_Month | Weekly Average
              ARMG0030001 | 2011 | 4 | 32465.8
                BDFD0020003 | 2011 | 4 | 43756.1
DRBY0320001 | 2011 | 4 | (NULL)
ARMG0030001 | 2011 | 3 | 23964.0
BDFD0020003 | 2011 | 3 | (NULL)
DRBY0320001 | 2011 | 3 | 21675.7

Hope this makes more sense, let me know if you need more details.


Thanks

James

[alansidman]

Add in your criteria "and Is not Null"

This will eliminate all records having a null value in the weekly average field

Alan

[springboardjg]

So the SQL query would be

SELECT Site_Serial, HSI_Year, HSI_Month, Weekly_Average
FROM tbl_SiteSerialsAndWeeklyAverages
WHERE HSI_Year=2011 AND HSI_Month=4 AND Weekly_Average IS NOT NULL

But by my understanding this would return 'ARMG0030001' and 'BDFD0020003' for April 2011, whereas I don't want the latter because it has a null value for the previous month.

[alansidman]

Your SQL statement as I read it should only return April 2011 Weekly Averages where the weekly Average is not null. It should not return anything from March. If it does, then your SQL statement that you are showing is not the SQL statement in your query. Have you copied it directly from your query?

Perhaps if you posted your db with dummy data it would be helpful. Also, before you post it, compact and repair it so it is not bloated for downloading.

Alan