query for real estate web site

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

General SQL Server Forums

New to SQL Server Programming

query for real estate web site

Author

Topic

trelamenos333
Starting Member

3 Posts

Posted - 2011-03-02 : 19:25:31

Hello my friends
Sorry for my english but i am from Greece...I am a web designer and i create a new web site for my father's real estate office..
I would like you to tell me if you can, how can i make my query..i am new in sql..
In my search for real estate properties i have 5 fields...my problem is that i don't know how to write a query where it can include all the possible options..For example..my fields are country,area,price,age and type..How sould i make this query when i don't know if someone will select all the option or some of them....
Thank you very much for your help!!!!

trelamenos333
Starting Member

3 Posts

Posted - 2011-03-02 : 19:56:24

pleaze....someone....

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-02 : 22:55:08

I have not tested it throughly but it should give you idea on how to start.

--Sample data

Create table #test
(
SrNo int identity,
Country varchar(50),
area int,
price int,
age int,
[type] varchar(50))

Insert into #test
Select 'XYZ', 100,5000,20,'Res' union all
Select 'XYZ', 1000,4200,25,'Off' union all
Select 'ABC', 500,500,50,'Res' union all
Select 'ABC', 200,5000,10,'Off' union all
Select 'SSS', 100,2500,20,'Res' union all
Select 'XXX', 1000,3000,15,'Res'

--Query
Declare @Country varchar(50),
@area int,
@price int,
@age int,
@type varchar(50)

--Set the Criteria
--Country = 'XYZ' and Age < 22
select @Country ='XYZ' , @age = 22

Select * from #test
where
(@Country is null or Country = @Country)
and (@area is null or area > @area)
and (@price is null or price <= @price)
and (@age is null or age <= @age)
and (@type is null or [type] = @type)

trelamenos333
Starting Member

3 Posts

Posted - 2011-03-03 : 09:50:06

Thank you very much my friend,so as i can understand you say with this query that when we don't know if an option is selected we say if it is null or not?am i wrong? thank you very much again for your reply my friend!

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-03 : 22:26:29

quote:
Originally posted by trelamenos333

Thank you very much my friend,so as i can understand you say with this query that when we don't know if an option is selected we say if it is null or not?am i wrong? thank you very much again for your reply my friend!

If the option is not selected then you don't assign any value to the variable. When no value is assigned to a variable then the variable always holds null.

You are welcome

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-03 : 23:33:27

its ok...did he bang your broad"?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-04 : 10:05:00

ok, show us the expected results

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources