1

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
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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