Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Nov 2008
    Posts
    93
    Thanks
    12
    Thanked 0 Times in 0 Posts

    if not null then select in a mysql statement

    hi

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

    Code:
    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

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

    Code:
    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.

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •