If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Enjoy an ad free experience by logging in. Not a member yet? Register.

Converting MS Access iif statement in SQL

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.

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.

Users who have thanked Old Pedant for this post:

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?

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.