Converting MS Access iif statement in SQL

[MaDmiX]

Hi All,

How would I write the following MS Access query in an SQL statement:

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

[guelphdad]

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.

[MaDmiX]

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

[Old Pedant]

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.

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.

[MaDmiX]

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

[Old Pedant]

Well, it's pretty ugly, but you could do this:

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

    ',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.)

[Old Pedant]

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.

[MaDmiX]

Originally Posted by Old Pedant

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