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 8 of 8
  1. #1
    Regular Coder MaDmiX's Avatar
    Join Date
    Feb 2012
    Location
    Charlotte, NC
    Posts
    162
    Thanks
    41
    Thanked 0 Times in 0 Posts

    Converting MS Access iif statement in SQL

    Hi All,

    How would I write the following MS Access query in an SQL statement:
    Code:
    SELECT IIf([Network_1]=-1,"Net-1",IIf([Network_2]=-1,"Net-2",IIf([Network_3]=-1,"Net-3",""))) AS Network
    FROM tblOnAirActivity;
    I have a table with three columns that are each true/false and I want to "merge" them such that the query will reflect one column with a unique string for each record where a given table column is true and an empty string (or nothing) where none of the table columns are true.

    Thanks in advance for your help.

    Kind regards,

    Ken

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    which database application are you using? All of them use SQL but syntax is different among databases.

    also providing sample data and expected results will also help those you are asking to assist you. what may be clear to you is not necessarily so to others.

  • #3
    Regular Coder MaDmiX's Avatar
    Join Date
    Feb 2012
    Location
    Charlotte, NC
    Posts
    162
    Thanks
    41
    Thanked 0 Times in 0 Posts
    Hi guelphdad,

    I am using phpMyAdmin as my database application and mySQL for the database itself. Here is an idea of what I have and how I want the results to look:

    ID Network_1 Network_2 Network_3
    1 -1 0 0
    2 0 0 -1
    3 0 -1 0
    4 -1 -1 0

    This is what I want to output:

    ID Network
    1 Net-1
    2 Net-3
    3 Net-2
    4 Net-1, Net-2

    ID #4 only just occured to me as I was typing out this example.

    Kind regards,

    Ken

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,324 Times in 4,290 Posts
    IIF in Access is virtually identical to IF in MySQL.

    You should be able to just change IIF to IF

    You can't use [....] with MySQL. Use `...` instead (those are backticks, the character on same keyboard key as the ~ tilde)
    Except you didn't need the [...] in Access and you don't need backticks in MySQL, either.

    And you should use apostrophes instead of quotes. MySQL will take either, but quotes are not ANSI compliant.


    Code:
    SELECT If( Network_1=-1,'Net-1',
                             If(Network_2=-1,'Net-2',
                                             If(Network_3=-1,'Net-3','')
                             )
           ) AS Network
    But your ID #4 would mean a different approach, completely.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    MaDmiX (10-23-2012)

  • #5
    Regular Coder MaDmiX's Avatar
    Join Date
    Feb 2012
    Location
    Charlotte, NC
    Posts
    162
    Thanks
    41
    Thanked 0 Times in 0 Posts
    Thanks Old Pedant,

    That worked perfectly! I didn't realize the IF could just be substituted. As I can see the need to handle cases where more than one network will return true, what would you recommend as the best approach to handle this?

    Thank you,

    Ken

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,324 Times in 4,290 Posts
    Well, it's pretty ugly, but you could do this:
    Code:
    SELECT SUBSTRING( 
               CONCAT(
                          If( Network_1 <> 0, ',Net-1',''),
                          If( Network_2 <> 0, ',Net-2',''),
                          If( Network_3 <> 0, ',Net-3','')
                     ), 
                2 ) AS Network
    You see it? We concatenate ",Net-X' if the value is needed else we concatenate a blank. So we might end up with
    Code:
        ',Net-1,Net-2,Net-3'
    And then we use SUBSTRING to lop off the first character. (If there were no characters--if the string was just ''--substring won't complain.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,324 Times in 4,290 Posts
    Oh, and I would use <> 0 instead of = -1 unless the field can have other possible values.

    I would bet you only got the -1 in there when you converted from Access YES/NO field to MySQL, yes? I'd be sorely tempted to change all the -1's to just 1 in the DB, but that's up to you.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Regular Coder MaDmiX's Avatar
    Join Date
    Feb 2012
    Location
    Charlotte, NC
    Posts
    162
    Thanks
    41
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Oh, and I would use <> 0 instead of = -1 unless the field can have other possible values.

    I would bet you only got the -1 in there when you converted from Access YES/NO field to MySQL, yes? I'd be sorely tempted to change all the -1's to just 1 in the DB, but that's up to you.
    You are correct. The -1 was from when I converted my Access database. This is my first web application. In the future I will definitely do things differently but I am in too deep to change now and it works fine as is.

    Thanks sooo much for all of your help!!!

    Ken


  •  

    Posting Permissions

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