using conditional where clauses in mysql select statement

There is a stored procedure sp_select_from_persons in MySql. And there are 3 parameters inside this procedure. age, class, group. These parameter values can be -1 or another value. And these parameters will be used in where clause.
Now I want to write where clause like that, if any value of these parameter is another than -1 then append them into where clause. For example:
if age = -1, class = 7 and group = 5 then select query will be:

SELECT * FROM persons p WHERE p.class = class AND p.group = group;

or if age = 25, class = -1 and group = -1 then:

SELECT * FROM persons p WHERE p.age = 25;

and etc.

I don’t want to use emnedded IF statements like below:

IF age > 0 THEN 
  IF class > 0 THEN
     IF group > 0 THEN
     SELECT * FROM persons p WHERE p.age = age AND p.class = class AND p.group = group;
     END IF
     ELSE ....... etc

Is there a better way to do this?

Answer

This is a single line query…. Try This

SELECT * FROM persons p 
WHERE p.age = case when age = -1 then p.age else age end
and p.class = case when class = -1 then p.class else class end
and p.group = case when group = -1 then p.group  else group end

Advertisement

Answer