基于同一表动态生成INTERSECT查询获取符合条件的ID

Got it, let's tackle this problem step by step. Here's how to dynamically generate the required INTERSECT queries for your Access table:

问题背景 #

First, let's recap your setup: you have an Access table named access with the following structure and data:

Your goal is to generate a SQL query using INTERSECT that filters IDs which meet all of your input condition groups (each group is {access: numeric value, value: string}).

Core Idea #

INTERSECT is perfect here because it returns only the records that exist in all of the input query results. Each condition group translates to a subquery that pulls IDs matching that single condition; stacking these subqueries with INTERSECT gives you the IDs that satisfy every condition.

How to Generate the Query Dynamically #

For each condition group {access: X, value: Y}, create a subquery like this:

SELECT id FROM access WHERE access = X AND value = 'Y'

Then connect all these subqueries with the INTERSECT keyword.

Example 1: Two Condition Groups #

Input: [{access:18,value:ab},{access:32, value:bc}]
Generated SQL:

SELECT id FROM access WHERE access = 18 AND value = 'ab'
INTERSECT
SELECT id FROM access WHERE access = 32 AND value = 'bc'

This returns IDs 1, 3 (the only IDs that match both conditions).

Example 2: Three Condition Groups #

Input: [{access:18,value:ab},{access:32, value:bc},{access:48,value:cd}]
Generated SQL:

SELECT id FROM access WHERE access = 18 AND value = 'ab'
INTERSECT
SELECT id FROM access WHERE access = 32 AND value = 'bc'
INTERSECT
SELECT id FROM access WHERE access = 48 AND value = 'cd'

This returns only ID 1 (the sole ID that meets all three conditions).

Quick Notes #

• Always wrap string values in single quotes ('Y') to avoid SQL syntax errors.
• Keep in mind: INTERSECT is supported in Access 2010 and later versions. If you're working with an older Access version, you'll need to simulate this logic with joins or nested subqueries instead.
• If your input could ever have zero condition groups, add a check to handle that edge case (though it sounds like you'll always have at least one group).

内容的提问来源于stack exchange，提问作者Kiran Kumar