Filter search query

Question

Hello,

I have a table with the columns City and ZoneCode. I have a dropdown list where I can select just one City or all. And another dropdownlist for the ZoneCode. Basically they are filters that only filter if I select a value from the dropdown lists.

Is there any recommended technique to do this?

edit: this table is only an example.

Thank you

Answer 1

You should move the City list and ZoneCode list to another tables, with adding a foreign keys in your main table (this is called normalization)

After that, you should add the keys as values to your drop down, and filter your main table with this keys.

This is classic decision for such situations

Answer 2

Since you've tagged this as sql-server, I'm going to assume that you're looking for recommendations on the database side, as opposed to how to do it in the front-end.

My recommendation is: Normalize it. If a ZoneCode belongs to a particular City, then you should have a table of Cities and a Table of ZoneCodes, with your Customer table (or whichever table has these columns now) referencing a ZoneCodeID.

Example schema:

CREATE TABLE Cities
(
    CityID int NOT NULL IDENTITY(1, 1)
        CONSTRAINT PK_Cities PRIMARY KEY CLUSTERED,
    CityName varchar(100) NOT NULL
)

CREATE INDEX IX_Cities_Name
ON Cities (CityName)

CREATE TABLE ZoneCodes
(
    ZoneCodeID int NOT NULL IDENTITY(1, 1)
        CONSTRAINT PK_ZoneCodes PRIMARY KEY CLUSTERED,
    CityID int NOT NULL
        CONSTRAINT FK_ZoneCodes_Cities FOREIGN KEY
            REFERENCES Cities (CityID)
                ON UPDATE NO ACTION,
                ON DELETE NO ACTION
    ZoneCode varchar(10) NOT NULL
)

CREATE INDEX IX_ZoneCodes_City
ON ZoneCodes (CityID)
INCLUDE (ZoneCode)

To filter it's as simple as:

SELECT ZoneCodeID, ZoneCode
FROM ZoneCodes
WHERE (@CityID IS NULL) OR (CityID = @CityID)

Or, if you only have the name:

SELECT z.ZoneCodeID, z.ZoneCode
FROM ZoneCodes z
INNER JOIN Cities c
ON c.CityID = z.CityID
WHERE (@CityName IS NULL) OR (CityName = @CityName)

If you can't do that - i.e. because this is freeform data, or because the data entry people might know the City but not the ZoneCode, etc., then all I can suggest is to make sure you're indexed properly:

CREATE INDEX IX_MyTable_City_ZoneCode
ON MyTable (City)
INCLUDE (ZoneCode)

Then your filter should be:

SELECT DISTINCT ZoneCode
FROM MyTable
WHERE (@City IS NULL) OR (City = @City)

...to get the ZoneCodes for a city, and:

SELECT FirstColumn, SecondColumn, ...
FROM MyTable
WHERE ((@City IS NULL) OR (City = @City))
AND ((@ZoneCode IS NULL) OR (ZoneCode = @ZoneCode))

Answer 3

Conditional WHERE clauses in TSQL are pretty lousy. I'd construct different queries for each condition and run the appropriate one depending on user input.

Answer 4

If you have a composite index on (City, ZoneCode):

SELECT  *
FROM    mytable
WHERE   City = @City
        AND ZoneCode = @ZoneCode
UNION ALL
SELECT  *
FROM    mytable
WHERE   City = @City
        AND @ZoneCode IS NULL
UNION ALL
SELECT  *
FROM    mytable
WHERE   @City IS NULL
        AND @ZoneCode IS NULL

If you don't have one and cannot create it:

SELECT  *
FROM    mytable
WHERE   City = COALESCE(@City, City)
        AND ZoneCode = COALESCE(@ZoneCode, ZoneCode)

The first query assumes that you cannot filter on ZoneCode without specifying the City.

Answer 5

Here is the best article I have ever seen about dynamic search in sql server: Dynamic Search Conditions in T-SQL