Applying grouping filter to sql query from dropdown

Below is some ASP code that lets me search through patient data.  I also can apply filters (date of service, department, etc.).  The one that's giving me trouble is the one for "location".

There's 3 different sites, but within each site there's sub locations.  Example, BIHP-IO and BMHP-IO would belong to BH (among others).  You can see the details in the case statement.


How can I search by location and make (for example) BIHP-OT and BIHP-H come up when I select BH from the drop down?  Right now it's sending the "BH" as the filter, and that's not a real location.

My complete code is attached.
			<select id="location" name='location'>
				If varSearchLoc <> "" then
					Response.Write("<option value='" & varSearchLoc & "'>" & varSearchLoc & "</option>")
					Response.Write("<option value=''>--Choose--</option>")
					Response.Write("<option value=''>--Choose--</option>")
				End If
				<option value='BH'>BH</option>
				<option value='EC'>EC</option>
				<option value='HD'>HD</option>

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I would try a nested query

strlocationsearch = "select * from table where location_table in (select sublocation from location_table where location = " & varSearchLoc  &" )"

Query may need work

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
If I understand your question, you might want to have two DDLs, one dependent on the other. The 2nd one is populated during run time when a selection is made in the 1st one. If this is correct, let me know and I'll provide more specifics with a code example.
IntercareSupportAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.