CODEWITHSUNDEEP
sqloracle-databasecase
cdh12
cdh12

Reputation: 27

Creating a case expression with a join statement; Oracle

I need all Library_ID to return as either a yes or a no. They are either 1, 2, or 3 so I need to change everything that's a 1, 2, or 3 to yes and all others to no. Here is my statement so far:

SELECT DISTINCT 
    Title, Publisher, Release_Date, ISBN, l.Library_ID
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.BRranch_ID
INNER JOIN 
    Library l ON br.Library_ID = l.Library_ID
ORDER BY 
    Title;

Now I'm stuck.

Upvotes: 0

Views: 44

Answers (2)

SwapnaSubham Das
SwapnaSubham Das

Reputation: 537

Try the below CASE expression:-

SELECT DISTINCT Title, Publisher, Release_Date, ISBN, l.Library_ID, CASE WHEN l.Library_ID IN (1,2,3) THEN 'YES' ELSE 'NO' END AS "YES_OR_NO" 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.BRranch_ID INNER JOIN Library l ON br.Library_ID = l.Library_ID ORDER BY Title;

Upvotes: 2

Bohemian
Bohemian

Reputation: 425258

Is it as simple as this?

SELECT DISTINCT
    Title, Publisher, Release_Date, ISBN,
    case when l.Library_ID in (1,2,3) then 'Yes' else 'No' end as Library_ID
From ...

Upvotes: 3

Related Questions