Results 1 to 5 of 5

Thread: Query with IF

  1. #1
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58

    Unanswered: Query with IF

    In the code below there is an IF function. For some reason adr.mail is selected when adr.crediteur_mail exists.

    Any ideas?

    Code:
    SELECT
    adr.id AS klant_id,
    CONCAT(adr.klantnaam, ' ',adr.vorm) AS klant,
    adr.adres AS adres,
    adr.postcode AS postcode,
    adr.plaats AS plaats,
    adr.tel AS tel,
    adr.fax AS fax,
    IF(adr.crediteur_mail > 0, adr.crediteur_mail, adr.mail) AS mail,
    lnd.language AS language
    FROM adressen AS adr
    LEFT JOIN landen AS lnd
    ON lnd.id = adr.land_id

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I read that as saying
    if there is something in adr.crediteur_mail, then use that as the mail address, otherwise use adr.mail in the variable called mail.

    ie the value used in the front end is called mail. if could just as easily have been implemented in the front end as in a query

    rather than use a numeric comaprison it may have been better to explicitly test the string column with an appropriate string function such as length
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    What you need to do is look at how the data is being stored in the database. If you are storing the data as a NULL when empty then you could use COALESCE:

    COALESCE(adr.crediteur_mail, adr.mail) AS mail

    If on the other hand you are storing it as an empty string '' then you will need the IF statement:

    IF(adr.crediteur_mail = '', adr.mail, adr.crediteur) AS mail
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by it-iss.com View Post
    If on the other hand you are storing it as an empty string '' then you will need the IF statement:
    alternatively,

    COALESCE(NULLIF(adr.crediteur_mail,''), adr.mail) AS mail
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    Quote Originally Posted by r937 View Post
    alternatively,

    COALESCE(NULLIF(adr.crediteur_mail,''), adr.mail) AS mail
    This code works

Posting Permissions

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