"Sue Cram" <[EMAIL PROTECTED]> wrote on 02/08/2005 01:18:48 AM:
> Thanks to the people who helped me with my IF statement question
> last night. Now I need to carry it one step further to a compound
> 'IF' statement. Again, can't find much information in the manuals --
>
> Several people sent me "IF (Adopted=1, 'Y', 'N') AS Adopted FROM
> Animal" and it works great. Now I want my report to show the
> English Language meaning for a field ('Location' in a table called
> 'Animal') that contains numeric values. In other languages it
> would be something like this (a compound if statement):
>
> IF Location=1
> MOVE 'Downstairs Cat Room' to Location
> ELSE IF Location=2
> MOVE 'Kitten Room' to Location
> ELSE IF Location=3
> MOVE 'Quarantine ' to Location
> ELSE MOVE 'Unknown' to Location
> END IF
>
> In other words, I want to test for the comparison being true, and if
> it isn't true I want to test it again for another value... I
> couldn't find any information about this construct in the manuals or
> past list messages. I also tried using the 'CASE .. WHEN .. THEN ..
> END' but couldn't figure out the syntax errors I was getting.
>
> Thanks again for your help and support-
> Sue
SQL can resolve that for you without an IF() as long as you have at least
partially normalized your data. Here's one way to solve this issue. Create
a new table, call it "Location".
CREATE TABLE "Location" (
ID int not null auto_increment primary key
, Locale varchar(25) not null
, UNIQUE(Locale)
);
Then populate the Location table so that you build a look-up list of
ID-Locale pairs (the ID values will be auto-generated)
INSERT Locations (Locale) VALUES ('Downstairs Cat Room'),('Kitten
Room'),('Quarantine');
Now do an OUTER JOIN to optionally match the rows of one table to the rows
of another (we need all of the rows from Animal and only those rows from
Location that match up). I strongly prefer using LEFT JOIN over RIGHT
JOIN. The JOIN will line up the rows in each table so that when the ON
condition(s) is/are true you will see data from one or both tables. In an
OUTER JOIN, those rows that don't satisfy the ON condition from the table
in the _opposite_ direction of the JOIN will get all null values in all of
their columns (even for columns that normally would not be null). By
checking for the NULL value we detect an Animal.Location value that isn't
listed in the Location table.
SELECT Name
, IF(Adopted=1,'Y','N') as Adopted
, IF(Location.Locale Is Null,'Unknown',Location.Locale) as
Location
FROM Animal
LEFT JOIN Location
ON Location.ID = Animal.Location;
This does two things for you:
1) It creates a master list of locations in the data and keeps it out of
your code.
2) It makes your query more flexible. If for some reason you wanted to
change the name of the 'Kitten Room' to 'Animal Nursery' then all you need
to change is that one row of data on the Location table. Since you should
write your front end to work off of the same list (the Location table, not
something hard coded) everything will automatically stay in sync (no code
changes). It makes everything a little easier to manage, trust me.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
