Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am new to my Mysql and need help regarding join I have a table called employee and this table contain some foreign key value like phone, address, sex. Table phone and address have 4 rows in it, I want to get value of all rows. I only know how to get single row value but failed to get multiple row value

this is the code I am using to get single row value from from foreign key:

SELECT e.employee_id, e.first_name, e.last_name,
e.address_id, e.phone_id, e.dob, e.maritial_id,
e.sex_id, e.photo, s.sex_label
FROM employee AS e JOIN sex AS s ON e.sex_id = s.id

SHOW CREATE TABLE phone

CREATE TABLE `phone` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `mobile` varchar(15) DEFAULT NULL,
 `mobile_one` varchar(15) DEFAULT NULL,
 `phone` varchar(15) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

SHOW CREATE TABLE address:

CREATE TABLE `address` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `address_one` varchar(50) DEFAULT NULL,
 `address_two` varchar(50) DEFAULT NULL,
 `state` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
share|improve this question
    
Please run SHOW CREATE TABLE phone\G and SHOW CREATE TABLE address\G and display their output in the question. –  RolandoMySQLDBA Jul 25 '12 at 15:57
    
I have updated the question –  AryanG Jul 26 '12 at 0:16
add comment

1 Answer

up vote 1 down vote accepted

Perhaps something like this:

SELECT e.employee_id, e.first_name, e.last_name,
CONCAT(addr.address_one,',',addr.address_two,',',state) fulladdr,
CONCAT(ph.mobile',',ph.mobile_one,',',ph.phone) phonenumbers,
e.dob, e.maritial_id, e.sex_id, e.photo, s.sex_label
FROM employee e
INNER JOIN sex s ON e.sex_id = s.id
INNER JOIN address addr ON e.address_id = addr.id
INNER JOIN phone ph ON e.phone_id = ph.id;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.