Mysql CASE to select a column instead of another if null

Hi all, i have a table that keeps labels for another table. i have structured like that:
fieldname (varchar50), fieldvalue (varchar50), label (text).

The idea is that i can join on that table and select the labels i want to display as table headers. This works perfectly.

But i want to select directly the column value if there's no label in the other table for that field at that value.

I'm using the following query:
SELECT t1.asa, count( t1.asepsis ) AS conteggio, t1.asepsis, l2.label AS lblasepsis, l1.label,
CASE l1.label
WHEN NULL
THEN t1.asa
ELSE l1.label
END AS lblasa
FROM dati AS t1
LEFT JOIN labels AS l1 ON l1.fieldvalue = t1.asa
AND l1.fieldname = 'asa'
LEFT JOIN labels AS l2 ON l2.fieldvalue = t1.asepsis
AND l2.fieldname = 'asepsis'
GROUP BY asa, asepsis

Open in new window

LVL 10
ienaxxxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johanntagleCommented:
Looks like IFNULL is the simplest solution for you: http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html#function_ifnull

SELECT ifnull(l1.label, t1.asa)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ienaxxxAuthor Commented:
Thanks,

it works. But...


... this is the result:


      lblasa
[BLOB - 1B]
BLOB - 1B]
[BLOB - 1B]
[BLOB - 1B]
[BLOB - 1B]


so if i choose to display blobs in the options i get the result.

Ho can i cast a varchar?
0
ienaxxxAuthor Commented:
i tried this:

SELECT t1.asa, count( t1.asepsis ) AS conteggio, t1.asepsis, l2.label AS lblasepsis, CONVERT(IFNULL(l1.label, t1.asa), "VARCHAR(50)")

FROM dati AS t1
LEFT JOIN labels AS l1 ON l1.fieldvalue = t1.asa
AND l1.fieldname = 'asa'
LEFT JOIN labels AS l2 ON l2.fieldvalue = t1.asepsis
AND l2.fieldname = 'asepsis'
GROUP BY asa, asepsis

Open in new window



and this:



SELECT t1.asa, count( t1.asepsis ) AS conteggio, t1.asepsis, l2.label AS lblasepsis, CAST(IFNULL(l1.label, t1.asa), VARCHAR(50))

FROM dati AS t1
LEFT JOIN labels AS l1 ON l1.fieldvalue = t1.asa
AND l1.fieldname = 'asa'
LEFT JOIN labels AS l2 ON l2.fieldvalue = t1.asepsis
AND l2.fieldname = 'asepsis'
GROUP BY asa, asepsis

Open in new window



But didn't work.
0
ienaxxxAuthor Commented:
Nevermind,  i found that i cannot use VARCHAR in CONVERT function, only CHAR.

Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.