Oracle中含JOIN语句的CASE表达式构建：Library_ID转Yes/No

Let's adjust your existing JOIN query to include the conditional logic you need. The core fix is swapping the raw l.Library_ID in your SELECT clause with a CASE expression that maps values 1, 2, 3 to "Yes" and all other values to "No".

Modified Query with CASE Logic #

SELECT DISTINCT 
    Title, 
    Publisher, 
    Release_Date, 
    ISBN,
    -- CASE expression to convert Library_ID to Yes/No
    CASE 
        WHEN l.Library_ID IN (1, 2, 3) THEN 'Yes'
        ELSE 'No'
    END AS Library_Status -- Descriptive alias for the transformed column
FROM Catalog_Item c 
INNER JOIN Book b ON c.Catalog_Item_ID = b.Catalog_Item_ID 
INNER JOIN Physical_Item p ON c.Catalog_Item_ID = p.Catalog_Item_ID 
INNER JOIN Branch br ON p.Branch_ID = br.Branch_ID -- Fixed typo: BRranch_ID → Branch_ID
INNER JOIN Library l ON br.Library_ID = l.Library_ID; -- Completed the Library join condition

Quick Breakdown of Changes #

• CASE Statement: Checks if l.Library_ID matches 1, 2, or 3. If true, returns "Yes"; otherwise returns "No".
• Column Alias: Added Library_Status to make the transformed column name clear in your results.
• Typo Fix: Corrected br.BRranch_ID to br.Branch_ID (likely a typo in your original query).
• Completed Join: Finished the final join condition between Branch and Library (your original query cut off mid-clause, assuming the join is on l.Library_ID).

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