Mysql Join to get multiple row value from

join;MySQL

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

Best Answer

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;
Related Question