select if (is there something like that?)

[emotears]

Is there a type of select if statement in sql?

 

what i mean is say you have 3 tables

tablea

tableb

tablec

 

and wanted to select something from tableb or tablec depending on what field1 in tablea was set to.

 

so if  tablea.field1 = 1 then you would select tableb.field1 and if tablea.field1 = 2 then select tablec.field1.

 

I dont know if this kind of function exist how how about doing it in sql. Right now i have 2 sql calls in php to do it. one to get the value of tablea.field1 and the on a php if statement depending on the result, but would like to create it into one statement.

 

hopefully that made some sense on what i was talking about.

[premiso]

Something like this "may" work:

 

SELECT CASE tablea.field1 WHEN 1 THEN tableb.field1 WHEN 2 THEN tablec.field1 END FROM tablea, tableb, tablec WHERE (where statement here etc);

 

Not really sure though, as I have never done it before , just found some stuff at http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html and went from there.

 

EDIT:

I just ran a rough test on my server, given the joins etc are done properly this should work just fine.

[fenway]

If the "condition" is a null value, COALESCE() works well too.

[emotears]

thanks for the info. I will keep both of them commands wrote down. will save me some unnecessary php code.

[emotears]

OK, so i was playing with it and got it to work...somewhat. seem to be getting a lot more output then it should and i dont know if i made a error somewhere and if so i dont know what i did heh.

 

sql code

SELECT quest.queststarter, 
CASE quest.starttype WHEN 'mob' THEN mob.name
WHEN 'item' THEN items.name END AS queststarter
FROM quest, mob, items
WHERE quest.id = 1

 

with this a lot of results and basically about 5 duplicates for each row in the mob or item table.

 

so i tried this

SELECT quest.queststarter, 
CASE quest.starttype WHEN 'mob' THEN mob.name
WHEN 'item' THEN items.name END AS queststarter
FROM quest, mob, items
WHERE quest.id = 1 AND
CASE quest.starttype WHEN 'mob' THEN
mob.id = quest.queststarter
WHEN 'item' THEN
items.id = quest.queststarter END

 

This gives me the correct mob/item name however it returns it 5 times.

 

am i doing something screwy in the statement that would return it 5 times?

[fenway]

You have no join conditions!

[emotears]

-facepalm- I can't believe i really did that.

 

I got it to work correctly now. thanks.