using if then in a stored procedure

[coolant]

( IN id INT )

 

select * from challenges as c, total_distance td where c.challenge_type = td.id and c.challenge_id = id;

returns what I want. I want to add onto it to do grab from different tables based on the challenge_type. I tried the following

 

select * from challenges as c, 

if ( c.challenge_type = 1 ) then
total_distance td where c.challenge_type = td.id
end if;

if ( c.challenge_type = 2 ) then
long_distance ld where c.challenge_type = ld.id
end if;

and c.challenge_id = id;

 

but that gives me syntax errors. What am I doing wrong?

 

Thanks!

[toplay]

You can perhaps do what you want in one query. I don't fully understand what you want or where 'id' is from.

 

The following query will basically retrieve data from challenges table when the where condition is satisfied, and maybe from the others. So, you may have null values when data is not found/matched.

 

select *

from challenges c

left join total_distance td

on (c.challenge_type = 1 and c.challenge_type = td.id)

left join long_distance ld

on (c.challenge_type = 2 and c.challenge_type = ld.id)

where c.challenge_id = 'a value'

;

 

Since the query is almost the same, why don't you just write/repeat the whole query in each 'if' condition?

 

 

 

You can't break up a select statement like the way you have it in stored procedures without using variables. Look at:

 

http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

 

 

 

hth and good luck.

 

[coolant]

could I do something like this -

 

@id = '1';
@type = select challenge_type from challenges where id = @id;

if ( @type = 1 ) then
select * from total_distance where id = @id;
end if;

if ( @type = 2 ) then
select * from long_distance where id = @id;
end if;

 

?

[Hooker]

Give this a try:

  SET @id = '1';
  SET @type = SELECT challenge_type INTO @type FROM challenges where ID = @id LIMIT 1;

  IF @type = '1' THEN
    SELECT * FROM total_distance WHERE id = @id;
  ELSEIF @type = '2' THEN
    SELECT * FROM long_distance WHERE id = @id;
  ENDIF;