From: Michael Stassen
Date: June 23 2006 5:52am
Subject: Re: if else statement
List-Archive: http://lists.mysql.com/mysql/199219
Message-Id: <449B81A8.60507@verizon.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8BIT

Song Ken Vern-E11804 wrote:
 > Hi,
 >
 > I'm trying to build a query in using SQL instead of doing it in Perl.
 >
 > I am trying to do something like this :
 >
 > If ((select col1 from table1 where id = 1) == 3)
 > Then
 > Select col2 from table2 where table2.id = 1;
 > Else
 > Select col2 from table3 where table3.id = 1;
 >
 > In Perl I would probably have to access the DB twice.
 >
 > Select col2 from table1 where if = 1;
 >
 > If (col2 == 3) {
 >  Select col2 from table2 where table2.id = 1;
 > } else {
 >  Select col2 from table3 where table3.id = 1;
 > }
 >
 > I've read the manual on subqueries but the example don't indicate how I
 > can do a conditional test using a subquery?
 >
 > Am I on the right track or is there another way to do this?

Maybe.  The first thing to realize, I believe, is that IF() is not a "control 
flow" function, despite what the manual says.  IF() is a function whose return 
value depends on a condition.  It does not allow you to "control flow" in the 
traditional programming sense.

Jørn Dahl-Stamnes wrote:
 > Maybe:
 >
 > (SELECT col2 FROM table2 left join table1 on table1.id=1 where table1.col1=3
 > and table2.id=1)
 > UNION
 > (SELECT col2 FROM table3 left join table1 on table1.id=1 where table1.col1<>3
 > and table3.id=1);
 >
 > I have not tested it...

That should probably work, though the JOIN conditions make me cringe.  Also, I 
have no idea why you are using a LEFT-JOIN (and backwards?).  I'd have done it 
this way:

   (SELECT t2.col2
    FROM table1 t1
    JOIN table2 t2 ON t1.id = t2.id
    WHERE t1.id = 1 AND t1.col1 = 3
   )
   UNION
   (SELECT t3.col2
    FROM table1 t1
    JOIN table3 t3 ON t1.id = t3.id
    WHERE t1.id = 1 AND t1.col1 != 3
   );

Thomas Lundström wrote:
 > Not sure what you're aming for here and how your data is structured but
 > why not use a join and alias and fetch all info in one select and then
 > solve what you need in your code?
 >
 > Something in the line of:
 >
 > select t2.col2 from_t2, t3.col2 from_t3
 > from table1 t1, table2 t2, table3 t3
 > where t1.id = t2.id
 >   and t1.id = t3.id
 >   and t1.id = 3
 >
 > Maybe you can do something like that?

That may be a start, but you have the wrong condition on t1.id, and you've left 
out any mention of t1.col1.  Also, explicit JOINs are better than implicit 
(using commas) JOINs.

Peter Lauri wrote:
 > SELECT IF(col1=3, (Select col2 from table2 where table2.id = 1), (Select
 > col2 from table3 where table3.id = 1)) FROM table1 WHERE id=1;
 >
 > That should do it.

I don't believe this will work, as the subqueries will return multiple rows 
where one is expected.

Song Ken Vern-E11804 wrote:
 > Hi Peter,
 >
 > Thanks you for you answers.
 >
 > Can I put SELECT statements inside the IF statement?
 > Mysql give error
 > ERROR 1064: Error in SQL syntax.
 >
 > Under Control Flow functions of the manual, it says
 > IF(expr1,expr2,expr3)
 >     If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2, 
else it returns expr3. IF() returns a numeric or string value, depending on the 
context in which it is used.
 >
 > But doesn't say what expr is. Examples only show numeric and string functions.

You've never mentioned your mysql version.  4.1+ is needed for subqueries.  In 
4.1.15, I find that

   IF(condition, (subquery1), (subquery2))

works so long as the subqueries are surrounded by parentheses and always return 
exactly one value.

In any case, I don't think subqueries are needed or helpful.  If there is at 
most one row in table2 and table3 for each id in table1, the following should work:

   SELECT IF(t1.col1 = 3, t2.col2, t3.col2)
   FROM table1 t1
   LEFT JOIN table2 t2 ON t1.id = t2.id
   LEFT JOIN table3 t3 ON t1.id = t3.id
   WHERE t1.id = 1;

Otherwise, I think the only option is the UNION query above.

Michael