Mysql – How to put IF statement in MySQL query


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…


Best Answer

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' 
    select * from FooValues;
    select * from FinalValues;

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