"Sue Cram" <[EMAIL PROTECTED]> wrote on 02/07/2005 02:57:59 AM:
> This is a pretty basic question, but I'm learning SQL from a book > and it's very very frustrating! > > I'm writing a report listing animals from our shelter and whether > they have been adopted. I am selecting the animal name field and a > field called 'adopted' from a table called 'animal'. The values in > 'adopted' are either: 1 (meaning yes, this animal was adopted) or 0 > (no, this animal has not been adopted). > > So far I: > "SELECT Name, Adopted FROM animal". This prints a column of animal > names and a column labeled "Adopted" with row after row of 0's and > 1's. I want my output report to say 'Y' if 'adopted' = 1 or 'N' if > adopted = 0. > > I've spent all day studying "Insert Into", "Update Where", "If... > Then", looking at SQL manuals (so far I've studied 3 of them), and > trying to find an example in existing code. I get syntax errors for > everything I try. This is a very simple thing that's done all the > time -- but I can't figure out how in SQL. > > Can someone help me? > Thanks, > Sue Don't feel bad. I've got a little bit of experience and I still need to hit the manual for some things. There are actually two functions you can use in your SELECT statement to make a choice between two or more values: IF() and CASE...WHEN ...THEN...END . In your situation, the IF() function will work and should be the easiest to understand. Try this: SELECT Name, IF(Adopted=1,'Y','N') as Adopted FROM animal; The IF() works like this: If the condition is true, use the first option, otherwise use the second. In your case, you want your SELECT statement to say 'Y' whenever Adopted is 1 and 'N' when it isn't. Make sense? Some additional reading: http://dev.mysql.com/doc/mysql/en/functions.html (chapter on MySQL functions) http://dev.mysql.com/doc/mysql/en/control-flow-functions.html (case and if functions) If you run into more questions later and can't find your answers in your books (not even in the online manual) come on back and we will help you through it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
