SQL - Inner Join

[springboardjg]

Hi All



I have a table, a snapshot of which is below.

 Site_Serial | HSI_Year | HSI_Month | Weekly_Average
HLDN0056003 | 2011 | 4 | 254177
HLDN0056003 | 2011 | 3 | 193223
KLWT0347001 | 2011 | 4 | 64728
KLWT0347001 | 2011 | 3 | [NULL]
LSPA0262001 | 2011 | 4 | [NULL]
LSPA0262001 | 2011 | 3 | 46921

I want to return a list of all site serials for the current month that has a weekly average value, so WHERE HSI_Year = 2011 AND HSI_Month = 4 AND Weekly_Average IS NOT NULL

However, I only want to return a site serial for the current month where the weekly average field IS NOT NULL for both the current month and the previous month. So from the above table I would only want to return...

 Site_Serial | HSI_Year | HSI_Month | Weekly_Average
HLDN0056003 | 2011 | 4 | 254177

... because the other site serials either have a [NULL] weekly average for the current month or the previous month.

Someone has suggested that I use an Inner Join to return the rows that I want. If this is the correct way to go about it could someone give me some pointers with the structure of the SQL query.


Thanks

James

[Rixxe]

I think this might be useful for you:
http://en.wikipedia.org/wiki/Join_(SQL)

Scroll down and look at 'Self Join', it would be worth reading and getting an idea on most of the joins used.
The examples also give you an idea of the structure that you should be using. If you get stuck come back with what you have.

Good Luck.