if and case statement on mysql

gvanaco

have somebody expierience withe these funtions.

Do you have somewhere good explenation (not on the mysql site)
Are there also example's availeble?

Thanks

gvanaco

AstroTeg

I've used case. Its not too rough. A cheesy example:

SELECT CASE myField1 WHEN myField1 > 10 THEN myField-10 ELSE myField+10 END AS caseresult FROM mytable

I believe thats correct. I found cases can really uglify the sql query. I found it was easier to build the case statement seperate from the SQL and then when I was sure I had it all worked out, I would copy and paste it in to the final sql statement. MySQL's IF() function works pretty well too and is along the lines of the CASE statement...

njm

..and a cheesy example of the MySQL IF function, which interestingly enough I only used for the first time today.

UPDATE prices SET prices.price = IF(RAND()>0.8,1,0);

hth, njm

gvanaco

SELECT CASE myField1 WHEN myField1 > 10 THEN myField-10 ELSE myField+10 END AS caseresult FROM mytable

Can I use the CASE statement for a LEF JOIN to different table's

WHEN myfield1 = '1' then (LEFT JOIN table1 ON ..... ) END
WHEN myfield1 = '2' then (LEFT JOIN table2 ON ..... ) END
WHEN myfield1 = '3' then (LEFT JOIN table3 ON ..... ) END
WHEN myfield1 = '4' then (LEFT JOIN table4 ON ..... ) END

the size amount of colums is different for table1, table2, table3, table4

I'll ask it before I realise that my idea isn't possible.

AstroTeg

From reading MySQL's web site (highly recommend it: mysql.com), it only looks like you can use these conditional statements within a SELECT or WHERE clause. Doesn't look like you can use them in the FROM clause.

If you're looking to conditionally include tables and columns, then this is an excellent task for PHP. Have PHP build the SQL query before sending it off to MySQL.

ultraslacker

Originally posted by gvanaco

Can I use the CASE statement for a LEF JOIN to different table's

WHEN myfield1 = '1' then (LEFT JOIN table1 ON ..... ) END
WHEN myfield1 = '2' then (LEFT JOIN table2 ON ..... ) END
WHEN myfield1 = '3' then (LEFT JOIN table3 ON ..... ) END
WHEN myfield1 = '4' then (LEFT JOIN table4 ON ..... ) END

the size amount of colums is different for table1, table2, table3, table4

I'll ask it before I realise that my idea isn't possible.

That is indicative of a problem with your database design.

gvanaco

Why ultraslacker is it a problem of databasedesign?

I cannot change the view of the database.

There is one mother table. Depending off which coordinationsystem I have to join it with an other table.
On the moment I have 15 different systems.

On the mother table there is a row the use a system. On one row of the 15 tables is rest of the data.

The size of the 15 tables is different

Is it possible to do the job?