Select when some fields are null...

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)

Select when some fields are null...

Author

Topic

iulianionescu
Starting Member

14 Posts

Posted - 2008-06-05 : 23:15:43

Here is the problem that I am struggling with. The structure is:

States: StateID, StateName
Counties: CountyID, CountyName
Cities: CityID, CityName
Zips: Zip, StateID, CountyID, CityID
Regions: RegionID, RegionName
Region_Data: RegionID, StateID, CountyID, CityID

The thing about the region is that it can be defined by states only, in which case CountyID and CityId are NULL, can be defined by Counties too, in which case only CityID is NULL or it can be defined up to City level, in which case all 3 are set to something. Example, Northeast would be all cities from northeast, but Pocono would be just some counties in PA, and so on...

The issue is now selecting all cities that belong into a region... Normally I would join the Zips table with the Region_Data table and retrieve all CityIDs... The issue is that, as I said, the CountyID and CityID might be null for some records, so I am not sure how to retrieve them?

I came up with one idea in which I create 3 temporary tables and I select in them all the records that have 1) only state, 2) only state and county, 3) all 3, then I join each and I union the results...

But I am wondering if there is a way to do the select in one shot?

Any idea? I appreciate your help,

Thank you,

Iulian

Regards,

Iulian

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-05 : 23:53:36

SELECT *
FROM Zips z
INNER JOIN Region_Data r
ON r.StateID=z.StateID
AND (r.CountyID = z.CountyID OR r.CountyID IS NULL)
AND (r.CityID = z.CityID OR r.CityID IS NULL)

dshelton
Yak Posting Veteran

73 Posts

Posted - 2008-06-05 : 23:54:48

SELECT * FROM Region_Data rd
JOIN Zips z
ON rd.StateID = z.StateID
AND ISNULL(rd.CountyID, z.CountyID) = z.CountyID
AND ISNULL(rd.CityID, z.CityID) = z.CityID

iulianionescu
Starting Member

14 Posts

Posted - 2008-06-06 : 11:31:51

Thank you! It worked like a charm!

Regards,

Iulian

Regards,

Iulian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources