asked on 2/24/2023

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

Hi,

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?

Thanks

SQL Microsoft SQL Server

Last Comment

Scott Pletcher

2/27/2023

slightwv (䄆 Netminder)

2/24/2023

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.

ASKER CERTIFIED SOLUTION

Bitsqueezer

2/25/2023

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

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

Scott Pletcher

2/27/2023

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 )
)
INSERT INTO dbo.tblStores VALUES
(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