0

Very new to SQL and trying to set up a users table, I have a check box for financial aid that outputs a BIT 0 or 1, ideally what I would like to do is convert this value to a YES or NO depending on the value. Right now I'm not entirely sure how to do this, I've tried adding

SELECT CASE `financial_aid`
         WHEN 1 THEN 'Yes' 
         WHEN 0 THEN 'No' 
       END As `financial_aid`
FROM `users`;

Currently this doesn't solve my problem, can anyone advise how I can fix this?

SQL

CREATE TABLE IF NOT EXISTS `users` (
  `User_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `player_name` varchar(40) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `dob` date NOT NULL,
  `parent_name` varchar(40) NOT NULL,
  `parent_email` varchar(40) NOT NULL,
  `parent_phone` varchar(40) NOT NULL,
  `experience` varchar(40) NOT NULL,
  `financial_aid` bit NOT NULL,
  `registration_date` datetime NOT NULL,
  PRIMARY KEY (`User_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10;

SELECT CASE `financial_aid`
          WHEN 1 THEN 'Yes'
          WHEN 0 THEN 'No' 
       END As `financial_aid` 
FROM `users`;
Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
styler
  • 15,779
  • 23
  • 81
  • 135
  • 1
    `Currently this doesn't solve my problem` - it gives an error? – ethrbunny Jan 06 '14 at 12:52
  • hey, currently it just registers as either 00000001 or 00000000 in my database instead of Yes or No – styler Jan 06 '14 at 12:56
  • Your query works fine : http://www.sqlfiddle.com/#!2/f4ae2/2 – StuartLC Jan 06 '14 at 12:57
  • Please elaborate more on why this doesn't solve your problem. Are you actually asking for a statement that will change the contents of the table? – MatBailie Jan 06 '14 at 12:59
  • Hey, yeah I would like my financial_aid column to display Yes or No, at the moment I just see the following: http://d.pr/i/uGyO – styler Jan 06 '14 at 13:04
  • If you want this persisted in the table, then you'll probably need a new column and a synchronizing [trigger](http://stackoverflow.com/questions/5222044/column-calculated-from-another-column) (MySql doesn't support Computed columns). Otherwise, just modify the query or presentation tier (e.g. screen) so that it maps it as you've done with the `case ... when` – StuartLC Jan 06 '14 at 13:42

1 Answers1

2

Try IF(true, x, y) function ...

SELECT IF(`financial_aid`, 'Yes', 'No') As `financial_aid` FROM `users`;

Or simply

SELECT case `financial_aid`
         WHEN TRUE 'Yes'
         ELSE 'No' END
        As `financial_aid`
FROM `users`;
Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82