advanced sql query help

Question

i have a table with products and the user can choose which regions and cities he wants to let them be visible to. he can choose ALL REGIONS and ALL CITIES from a drop down menu and then i will put in the columns = 0.

the the buyers can choose which city they want to watch products from so they aslo have a drop down menu to pick a region and/or city. and they can also choose products from ALL REGIONS and/or CITIES.

the question is how this advanced query will look like.

1. so if the seller has clicked on one specific region but all cities, then if the buyer picks that region he can pick whichever city he wants to. that product will still be shown.

2. the buyer can instead of picking a city pick ALL CITIES. eg. then he will see all threads despite which city they posted to.

Im a noob in sql so it would be good if you guide me to pick the right commands for this query. this was a little bit to complex for me, i have no idea where to start...

Answer 1

I think I understand what you mean. Your query can have two arguments, one for city and one for region.

In the city dropdown, if the user selects a city, say Los Angeles, then the @city argument would contain the city (let's say a code like LAX). If they select "All Cities" then the @city argument would have a the value of NULL.

Same for region.

Then your query could be something like:

SELECT ...
FROM city_tbl
INNER join region_tbl ON ...
WHERE ( @city IS NULL or @city = city_tbl.city_code )
AND   ( @region IS NULL or @region = region_tbl.region_code )
AND ...

So if they've selected "All Cities", @city is NULL and they'll see all of them, otherwise their choice of a specific city must be matched to the city_tbl.city_code.

I suspect you have something a bit more complicated in mind, but hopefully this is on the right track.