SQL实现库存数据库中用户特定物品的单值Yes/No查询

Hey there! The issue with your current query is that the LEFT OUTER JOIN creates a row for every asset a user owns. So if a user has both a DELL and non-DELL asset, you'll get multiple rows for that user with mixed YES/NO values—definitely not what we want when we just need a single "does this user own at least one DELL?" answer.

Here are two solid solutions to fix this:

1. Use GROUP BY with MAX() #

This approach groups all assets per user and picks the highest-priority value (since 'YES' is alphabetically higher than 'NO', MAX() will return 'YES' if there's at least one match).

SELECT 
    USER_DB.USER,
    MAX(CASE WHEN ASSETS_DB.HARDWARE_REF = 'DELL' THEN 'YES' ELSE 'NO' END) AS DELL
FROM USER_DB
LEFT OUTER JOIN ASSETS_DB ON USER_DB.USER = ASSETS_DB.OWNER
GROUP BY USER_DB.USER;

How it works: #

• GROUP BY USER_DB.USER collapses all rows for the same user into one.
• MAX() scans all the CASE result values for the user. If any of them is 'YES', that becomes the final value; if all are 'NO' (or the user has no assets), it returns 'NO'.

2. Use EXISTS Subquery (More Efficient) #

This method checks directly if a user has at least one DELL asset without joining all their assets first. It's often faster, especially with large datasets, because it stops searching as soon as it finds a matching asset.

SELECT 
    USER,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM ASSETS_DB 
            WHERE ASSETS_DB.OWNER = USER_DB.USER 
              AND ASSETS_DB.HARDWARE_REF = 'DELL'
        ) THEN 'YES' 
        ELSE 'NO' 
    END AS DELL
FROM USER_DB;

How it works: #

• The EXISTS subquery runs once per user. If it finds even one DELL asset owned by the user, it returns true, so the CASE statement outputs 'YES'.
• If no matching assets exist (or the user has no assets at all), it returns 'NO'.

Both solutions will give you the clean, single-row-per-user result you need:

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