IF statement in MySQL

jrhitokiri

Can you do something like:

Expand|Select|Wrap|Line Numbers

 
select itemType from reservables where reservableID = #input#

if (itemType='room')

 select * from rooms

else select * from items

Something like that? Thanks.

Kind regards
JR

jrhitokiri

okay, now I' trying something like this:

Quote:

SELECT IF(reservableType = 'item',
"Select * From items where items.itemID = 2",
"Select * From room where room.reservableID = 2")
FROM reservables WHERE reservables.reservableID = 2

but it still does not work. :(

Atli

Hey.

SQL queries aren't really meant to include that level of logic.
For things like that, you would be better of trying a Stored Procedure

Something along the lines of:

Expand|Select|Wrap|Line Numbers

 DELIMITER %%

CREATE PROCEDURE `Example`(IN pInput VARCHAR(64))

BEGIN

    -- Declare all variables we plan on using

    DECLARE doLoop BOOLEAN DEFAULT TRUE;

    DECLARE cCondition VARCHAR(64);
 
    -- Create query cursors, so we can read the results.

    DECLARE queryCursor CURSOR FOR 

        SELECT `something`

        FROM `parentTable`

        WHERE `condition` = pInput;
 
    -- Execute the query

    OPEN queryCursor;
 
    -- Fetch the first row of results

    FETCH queryCursor INTO cCondition
 
    -- Execute a query based on the results

    IF cCondition == 'something' THEN

        SELECT stuff FROM firstTable WHERE condition = pInput;

    ELSE

        SELECT stuff FROM secondTable WHERE condition = pInput;

    END IF
 
    -- Close the cursor

    CLOSE queryCursor;

END%%

DELIMITER ;

And, once you have created the procedure, you call it in your code like so:

Expand|Select|Wrap|Line Numbers

CALL `Example`('input value');

Atli

You could also try to use joins to do this, if you don't like procedures.

Something like this might work:

Expand|Select|Wrap|Line Numbers

 SELECT * FROM parentTable AS p

LEFT JOIN firstTable AS f

    ON f.itemID = p.itemID

LEFT JOIN secondTable AS s

    ON s.itemID = p.itemID

WHERE p.itemType = 'Something'

AND   p.itemID = 2;

jrhitokiri

wow, that makes sense! Although the code is more than twice as long, I can still use this. Didn't know there were procedures in MySQL. All I ever did was SELECT,INSERT,UPDATE, etc. Maybe I should start reading about MySQL more. :-/

Thanks, this was quite helpful.

Regards,
JR

IF statement in MySQL

re: IF statement in MySQL

re: IF statement in MySQL

re: IF statement in MySQL

re: IF statement in MySQL

Similar MySQL Database bytes

/bytes/development

/bytes/it

/bytes/about