CODEWITHSUNDEEP
mysqlsqldatabasecase
emoleumassi
emoleumassi

Reputation: 5149

MySQL Select when case

i have those tables and i want to check the same attribute at the same time: 

Person
id---name
1----Paul
2----Tom  
3----Jim 

Age
id---wert------personId  
1----28--------1 
2----25--------1  
3----30--------3

i want to do something like this. 

    select * from Person p, Age where personId = p.id and CASE WHEN 
name = 'Paul' THEN Age > 28 WHEN name = 'Tom' THEN Age <....

How it is possible? With a CASE THEN in the WHERE clause? Please don't think about the structure of the table but only about the principle.

Any Ideas? Thank

Upvotes: 0

Views: 84

Answers (2)

ABrowne
ABrowne

Reputation: 1604

CASE WHEN THEN I believe need to be part of an expression, not an expression themselves. It's common use case is in the select, however you can use it in the where.

In a select this would be:

select 
    case name
        when 'paul' then 28
        when 'jim' then 30
    end
from X

To use it within an where you would do this:

select * from X where age = case name when 'jim' then 28 else 30 end

Depending on what you are attempting to achieve, you may want to consider OR statements instead of case in your where clause.

Upvotes: 0

Krazibit312
Krazibit312

Reputation: 390

What you're trying to do is possible like so

 select 
       * 
  from 
    Person p, Age  
  where personId = p.id 
 and CASE 
          WHEN name = 'Paul' THEN Age > 28 
          WHEN name = 'Tom' THEN Age <....  
          WHEN expr then expr that evals to bool
       END

Upvotes: 1

Related Questions