How to put IF statement in MySQL query

Question

Hey all!

I have a query that is simply selecting from 3 tables.

1. select from the comments table for all comments on an article

2. select from UserID table to find what kind of user it is

3. IF the user is one type, search one table...ELSE search another table for the final info.

How would I go about this? I'm a little new at MySQL stuff, so i totally appreciate your patience!

PS - let me know if this isnt clear...

Thanks!

Answer 1

I wouldn't put them all in a single query. The code is much more readable if you break the operation down to smaller steps.

If performance is not a concern, you could get the user type first and then run another query depending on the type.

For a more correct way, you should consider using a stored procedure.

Answer 2

OK So lets say the type condition is - If the user if of type 'foo' search table 'foovalues' else search table 'finalvalues'.... Assuming the table structures are as follows

Comments CommentID UserID ArticleID

Users UserID UserType

 Declare TestUserType varchar(3);

 select * from Comments where ArticleID = <inputid>; //Returns the comments 

 select TestUserType = UserType from Users where UserID = <inputuser>; //Returns the usertype for a user and assigns it to a variable

 if TestUserType = 'foo' 
   begin 
    select * from FooValues;
   end
 else
   begin 
    select * from FinalValues;
   end

Disclaimer: The above SQL should work in mySQL but it's been awhile since I worked on that DB and I don't have access to it right now so the SQL above may encounter syntax errors.

You can put the SQL in a stored proc as well - if you are doing that mySQL has this thing about delimiters you might want to look out for - I blogged about it here