[polska03]

Hello I have a table which stores values as key and value in a text field (e.g 0,male \n 1,female). I want to just want to retrieve the male or female texts according to the key. I want to do this is mySql opposed to using php in the website to do it later.So essentially I have another table that stores the sex ( 0 or 1) and I want the query to reference to the table that contains 0,male \n 1,female and get the appropriate label(male or female) based on the value I give (0 or 1). Is this possible? I am using MySQL

This post has been edited by polska03: 30 June 2012 - 11:39 AM

[macosxnerd101]

Why use numbers to store the sex? Use an enum type, defining 'MALE' and 'FEMALE' as constants.

[polska03]

I know that is the smart way, but this is they way this program stores it. Is it possible?

[macosxnerd101]

Yes. Just use a basic SELECT query.

SELECT col2
FROM table_name
WHERE col1 = 'some_value'

[polska03]

Sorry don't understand. So if I have a table (person) that stores person_id and sex (where sex is either 0 or 1) and I want to retrieve what the sex is in terms of male or female which is stored in another table that keeps it as (0,male \n 1,female) which php is used later to parse it, how would I retrieve male or female from that command?

[JackOfAllTrades]

Persons:

CREATE TABLE `persons` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(64) NOT NULL,
 `last_name` varchar(64) NOT NULL,
 `sex_id` tinyint(1) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `sex` (`sex_id`),
 CONSTRAINT `persons_ibfk_1` FOREIGN KEY (`sex_id`) REFERENCES `sexes` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Sexes table:

CREATE TABLE `sexes` (
 `id` tinyint(1) NOT NULL AUTO_INCREMENT,
 `sex` varchar(6) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Example data:

Sexes:

INSERT INTO `sexes` VALUES(1, 'FEMALE');
INSERT INTO `sexes` VALUES(2, 'MALE');

Persons:

INSERT INTO `persons` VALUES(3, 'Joe', 'Bloggs', 2);
INSERT INTO `persons` VALUES(4, 'Jane', 'Bloggs', 1);

Query to retrieve data:

SELECT p.first_name, p.last_name, s.sex from persons p inner join sexes s on p.sex_id=s.id

Retrieval results:

|Jane|Bloggs|FEMALE
|Joe|Bloggs|MALE

[baavgai]

Exactly!

Though, um, I'd go with a gender table, and gender_id, gender_desc. "sexes" just sounds like an act that could be illegal in some states. Plurality in table names general end of sounding a little off in practice.

Note, for something as trivial as a value pair, you might just opt for a simple decode.

SELECT first_name, last_name, 
   (case gender when 0 then 'Male' when 1 then 'Female' else 'NA' end) as gender_desc
   from person

[JackOfAllTrades]

You're right about gender vs. sex in these PC times.

And the table's names? Habit from my recent experience with CakePHP, which uses a plural table name convention.