Avatar of paultran00
paultran00Flag for United States of America

asked on 

SQL SERVER 2019 - Filter using a lookup table instead of hardcoding the query WHERE clause.


I have a table tblMain.  

I have a query that selects data from tblMain and if the field STORE has a value that is in the list of stores in my WHERE clause, then I want to have a new field ANSWER to have the value 'Y' else 'N'.

Because the list of stores in my WHERE clause needs to be maintained, I'd rather create a lookup table tblStores so that maintenance is easier to modify the list.

The query I want would select data from tblMain and join to tblStores, then creates a new column ANSWER with the values 'Y' or 'N'.

What would this query look like?


SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Scott Pletcher
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

select col1, col2,
   case when store in ('a','b','c') then 'Y' else 'N' end
from tblMain
where store in ('a','b','c') 

Open in new window

Before you ask, no pretty sure you cannot magically access the where clause values in the select portion of the query.
Avatar of Bitsqueezer
Flag of Germany image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
I strongly urge you to add a key to the "stores" table so that you can pre-define different lists of match stores, then choose any of the list (or even lists) programmatically.  For example:

CREATE TABLE dbo.tblStores (
    storeId int NOT NULL,
    store int NOT NULL,
    CONSTRAINT tblStores__PK PRIMARY KEY ( storeId, store )
    (1, 1), (1, 3), (1, 7), /*first list of store match values*/
    (2, 2), (2, 3), (2, 5) /*second list of store match values, ...more lists...*/

DECLARE @storeId int
SET @storeId = 1 --set to 2 to pull second list of stores instead

SELECT m.store, m.col1, m.col2, CASE WEHN s.store IS NULL THEN 'N' ELSE 'Y' END AS answer
FROM tblMain m
LEFT OUTER JOIN tblStores s ON s.storeId = @storeId AND s.store = m.store
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo