if not null then select in a mysql statement

[chidge]

hi

I am wondering if there is a better way to do this?

SELECT  
(SELECT title from jnl_article WHERE blog_id = jnl_id) AS title,
DATE_FORMAT(date, '%D of %M %Y at %l:%i %p') AS datetime,
IF(folder IS NULL, 'none', folder) AS folder,
IF(folder IS NULL, 'none', (SELECT filename FROM jnl_main_image WHERE blog_id = jnl_id)) AS filename,
IF(folder IS NULL, 'none', (SELECT caption FROM jnl_main_image WHERE blog_id = jnl_id)) AS caption  
FROM jnl
WHERE status = 2
ORDER BY date DESC LIMIT 15

basically i get the title from another table in a subquery and then if there is a value in the folder option in the jnl table the statement will then go on to get the filename and the caption from jnl_main_image

What I am wondering is if there is a better way to do this? Can I do an

if folder then do this statement?

I am a little usureas to how to go about this and have a good look over the mysql docs - looks like a case statement or an if but ifs seem to just be able to give one value back.....

sorry if its a silly noobie question

and thanks in advance

[Fumigator]

There is a specific function to handle nulls, called IFNULL().

http://dev.mysql.com/doc/refman/5.0/...unction_ifnull

This function is probably not what you want though-- it returns a column if it's not null; if it is null, it returns the 2nd argument you provide.

Perhaps a LEFT JOIN would be better? Perhaps not, if there's a match in jnl_main_image that you don't want to grab if folder is null... hey if your query is giving you what you want with reasonable performance, go with it.

[guelphdad]

Actually COALESCE is standard for what you describe Fumigator. I don't believe IFNULL is standard sql. and you can use a select with it too.

COALESCE(folder,(SELECT filename FROM jnl_main_image WHERE blog_id = jnl_id)) AS filename

will either select the value in folder or if null will do the select statement instead.

[Fumigator]

Ah ok, good to know. I use NVL() in Oracle and IFNULL() in DB2 and MySQL; I suppose I can just use COALESCE() in all of em!