Outer join? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Outer join?

Author

Topic

pmwhelan
Starting Member

5 Posts

Posted - 2010-08-26 : 05:11:42

Hi

My query below works fine

select s.stationcode, ad.[year] from stations AS s INNER JOIN annualdata as ad ON s.stationcode = ad.stationcode WHERE ad.[year] = 1750

I have two tables
Stations contains a list of buildings around the world.
There is one stationcode for each building.

AnnualData contains a list of data collected every year from each station.

Sometimes a station will have annual data for a year, sometimes it will not.

My SQL above will show all stations that HAVE data for that year.

Is it possible to do a SELECT where it shows ALL the stations and if a station has or does not have data for a year?
And to create a column 'HasDataForYear' which will be true or false.

thanks

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-08-26 : 06:34:01

Try this:


SELECT s.stationcode, ad.[year], CASE WHEN ad.[year] IS NULL
                                           THEN 0
                                      ELSE 1
                                 END AS HasDataForYear
  FROM stations AS s
       LEFT OUTER JOIN
       annualdata as ad
       ON s.stationcode = ad.stationcode
          AND ad.[year] = 1750

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources