Where Clause Problems - 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 2000 Forums

SQL Server Development (2000)

Where Clause Problems

Author

Topic

WDM
Starting Member

1 Post

Posted - 2006-05-02 : 21:14:45

Hi,

I have been searching everywhere and trying everything but cannot get this where clause to work and need some help desperately because it is starting to drive me insane!

This is the query I have:

select top 10 PropertyID, StateName, SuburbName, SuburbPostcode, CatchyTitle, Summary, SalePrice, PropertyTypeID, NoBedrooms, NoBathrooms, NoGarages, WidthAtCurb, WidthAtBack, LenghtOfBlock, ListingTypeID from dbo.View_PropertyAlertSearch

where StateID = 1 and SuburbID in (7920) and PropertyTypeID in (2,1,3) and SalePrice >= 200000

and NoBedrooms BETWEEN 1 AND 4 and NoGarages BETWEEN 1 AND 4

What I want to do is only apply the 'and NoBedrooms BETWEEN 1 AND 4 and NoGarages BETWEEN 1 AND 4' part if a PropertyTypeID is NOT 3. I have tried everything and searched everywhere and have just about lost hope :( if anyone can help point me in the right direction I would greatly appreciate it.

Thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-02 : 21:27:14

have you tried CASE?

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-02 : 21:28:13

is it something like this ?


where StateID = 1 
and   SuburbID in (7920) 
and   SalePrice >= 200000
and   (
          PropertyTypeID in (3) 
      or  (
              PropertyTypeID in (1,2) 
          and NoBedrooms BETWEEN 1 AND 4 and NoGarages BETWEEN 1 AND 4
          )
       )

and NoBedrooms BETWEEN 1 AND 4 and NoGarages BETWEEN 1 AND 4

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources