Problem with multiple location search

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

Transact-SQL (2000)

Problem with multiple location search

Author

Topic

savvy
Starting Member

48 Posts

Posted - 2005-09-20 : 08:08:18

I am building job search engine. I got one listbox and one dropdownlist of place and region. For example if i select East Anglia from places the dropdownlist of regions it will display Any region, Cambridgeshire, Norfolk, Suffolk and if I Select Greater London it will display Any region, London, London-Central, London-East etc.. etc..
I am searching for jobs with respect to regions so is it possible that if i select say East Anglia in the listbox and Any region in the dropdownlist and i can search with all the regions in that section selected.
At the moment i'm using this stored procedure which doesn't include the above idea. Is there any way to modify this
Thanks in Advance

SELECT DISTINCT J.*
FROM JobDetails AS J
JOIN
(
SELECT DISTINCT J1.JobID
FROM JobDetails AS J1
inner join dbo.Split1(@list, ' ') AS S
ON J1.JobPosition LIKE '%' + S.Data + '%'
AND (@loc='0' OR J1.Location LIKE '%'+@loc+'%')
AND (@region='0' OR J1.Location LIKE '%'+@region+'%')
AND (@type='0' OR J1.JobType=@type)
AND (@a IS NULL
OR (J1.JobDate BETWEEN
DATEADD(dd,-@a-1,getdate())
AND GETDATE()
)
)
) J1 ON J1.JobID = J.JobID

savvy

Kristen
Test

22859 Posts

Posted - 2005-09-20 : 08:55:06

Add a "Place" column [e.g. representing East Anglia] column to your Region table so that your query for East Anglia can be represented as any of Suffolk, Norfolk, Essex ... ??

So our JobDetails table would contain a column for "Region".

For a given job the "Region" chosen would be, say, Suffolk.

In your query you then join the JobDetails table to the Region table, and your where clause has

(@regionID IS NULL OR REGION.ID = @regionID)
AND (@placeID IS NULL OR REGION.PLACE = @placeID)

where with @regionID in this example would be the ID for Suffolk and @placeID would be the ID for East Anglia (specifying either of those could find all Jobs in Suffolk, the East Anglia one would be broader)

Don't know if I've explained that very well, but your code

J1.Location LIKE '%'+@region+'%'

looks a little scary because it looks like you are relying on people typing in "Suffolk" in the location, and they are bound to make typos or call it something different - much better to force the people doing the data entry to pick a value from a fixed list, rather than use free text.

Kristen

savvy
Starting Member

48 Posts

Posted - 2005-09-20 : 10:20:56

That works.
Thank u very much
I was also planning to make the people to pick from a fixed list of places rather than entering their own choices.

cheers mate

savvy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources