CODEWITHSUNDEEP
mysqlsqlcase
KnowNothing
KnowNothing

Reputation: 69

How to set values using case statement in a select query?

I have a query like this

  left outer join ( select  tm.item_id as item_id,function_role =(CASE 
   mf.function_name
   when mf.function_name is not null 
   then mf.function_name
   else 'admin' 
   end)
   from team_members tm 
   join mem_function mf on mf.function_id=tm.function_id
   ) bnu on bnu.item_id = p.pro_id

When there's NO value in the function_name column of mem_function table,I want to set that value as 'admin'. when i tried this it's throwing "From keyword not found where expected" anyway to resolve this?

Upvotes: 2

Views: 349

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Is this what you want?

left outer join
(select tm.item_id as item_id,
        coalesce(function_name, 'admin') as function_role
 from team_members tm join
      mem_function mf
      on mf.function_id = tm.function_id
) bnu
on bnu.item_id = p.pro_id

Although you can do this using case, coalesce() is really the better solution.

I think your problem involves the function_role =. That assigns a variable, not a column name.

EDIT:

Your problem seems to be in the outer query. So you want:

select . . ., coalesce(function_name, 'admin') as function_role
from . . .
left outer join
(select tm.item_id as item_id, function_name
 from team_members tm join
      mem_function mf
      on mf.function_id = tm.function_id
) bnu
on bnu.item_id = p.pro_id

You probably don't need the subqueries, but that is another matter.

Upvotes: 2

Related Questions