报表开发：判断主表行是否在业务表中存在并标记Y/N

Hey there! Let's figure out how to get that color usage report sorted. You need every entry from your COLOR_MASTER table, plus a quick "Y"/"N" flag showing if that color's being used in CURRENT_PROJECTS—right? Design view can be tricky for this kind of conditional logic, so let's jump straight to SQL which will get you exactly what you need.

The key here is using a LEFT JOIN to keep all rows from your COLOR_MASTER table (even if a color isn't used in any project), then adding a conditional check to set your "used" flag.

For Most Databases (SQL Server, MySQL, PostgreSQL, etc.) #

Use a CASE statement to check for matching entries in CURRENT_PROJECTS:

SELECT 
    cm.COLOR_NAME,
    -- Mark 'Y' if the color exists in CURRENT_PROJECTS, else 'N'
    CASE 
        WHEN cp.COLOR_NAME IS NOT NULL THEN 'Y'
        ELSE 'N'
    END AS IS_USED
FROM 
    COLOR_MASTER cm
LEFT JOIN 
    CURRENT_PROJECTS cp ON cm.COLOR_NAME = cp.COLOR_NAME
-- Optional: Remove duplicates if a color is used in multiple projects
GROUP BY 
    cm.COLOR_NAME;

For Microsoft Access #

Access uses IIF() instead of CASE, so adjust the query like this:

SELECT 
    cm.COLOR_NAME,
    IIF(cp.COLOR_NAME IS NOT NULL, 'Y', 'N') AS IS_USED
FROM 
    COLOR_MASTER cm
LEFT JOIN 
    CURRENT_PROJECTS cp ON cm.COLOR_NAME = cp.COLOR_NAME
GROUP BY 
    cm.COLOR_NAME;

Why This Works #

• The LEFT JOIN ensures every color from COLOR_MASTER stays in your results, even if there's no match in CURRENT_PROJECTS.
• The conditional statement checks if the joined COLOR_NAME is null (no match = 'N') or exists (match = 'Y').
• The GROUP BY clause removes duplicate rows if a color is used in multiple projects (so you only get one entry per color with its flag).

If you were stuck in design view, it's because visual query builders often struggle with custom conditional flags like this—writing the SQL directly gives you full control.

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