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
