CODEWITHSUNDEEP
sql-servert-sqlselectcaseinner-join
jampez77
jampez77

Reputation: 5241

SQL Server query with case in select statement with multiple INNER JOINS

I have a query with returns a set of data about projects. The issue however is that some of the projects don't have a valid currency id.

So i'm trying to build a query that will, if the currency id is 0, use the id 140 instead. This is what I have so far but returns no results if the currency id is 0.

   SELECT *,
   CASE
   WHEN p.currencyid=0 THEN 140
   END
   FROM projects AS p 
   INNER JOIN businesssectors AS bs ON bs.businesssectorid=p.businesssectorid 
   INNER JOIN currencies AS c ON c.currencyid=p.currencyid
   INNER JOIN plants AS pl ON p.plantid=pl.plantid 
   WHERE p.projectid='195'

Upvotes: 0

Views: 57

Answers (1)

Simone
Simone

Reputation: 1924

Try this, it should work

   SELECT *,
   CASE
   WHEN p.currencyid=0 THEN 140 ELSE p.currencyid 
   END
   FROM projects AS p 
   INNER JOIN businesssectors AS bs ON bs.businesssectorid=p.businesssectorid 
   INNER JOIN plants AS pl ON p.plantid=pl.plantid 
   LEFT OUTER JOIN currencies AS c ON c.currencyid=p.currencyid
   WHERE p.projectid='195'

Upvotes: 2

Related Questions