基于含外键的3张表，使用IN子句查询指定条件的用户姓名

Alright, let's tackle this query step by step. Based on your table structure and requirements, here's how you can use an IN clause to get the first and last names of Admin users located on the 3RD Floor:

SELECT fNAME, lNAME
FROM USERNAME
WHERE USER_POSITION = 'Admin'
  AND USER_LOCATION IN (
    SELECT LOCATION
    FROM uLOCATION
    WHERE FLOOR_LOCATION = '3RD FLOOR'
  );

代码解释： #

• 子查询部分: First, we fetch all location IDs (LOCATION) from the uLOCATION table where the floor is exactly '3RD FLOOR' (note the case matches the check constraint in your table definition).
• 主查询部分: We then filter the USERNAME table to only include rows where:
  • The user's position is 'Admin' (matching the allowed values in your check constraint),
  • The user's location ID (USER_LOCATION) is present in the list of location IDs we retrieved from the subquery.

This approach leverages the foreign key relationship between USERNAME.USER_LOCATION and uLOCATION.LOCATION to link user data with their floor location, and uses the IN clause to efficiently filter based on the subquery results.

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