Using a join to return multiple values of the same column?

Question

I have two tables

1. user
2. Rank

In the Rank table, I saved rank_id and supervisor_id. In the Rank table, I stored the rank name. The rank_id and supervisor_id are foreign keys from the Rank table.

Since I have to select both rank_id and supervisor_id from same table(Rank), how do I join both tables to get the result with name, rank name and supervisor?

DDL

CREATE TABLE IF NOT EXISTS `user` (
 `id` int(9) NOT NULL AUTO_INCREMENT,
 `Name` varchar(90) NOT NULL,
 `rank_id` int(4) NOT NULL,
 `supervisor_id` int(4) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
--
-- Dumping data for table `user`
--
INSERT INTO `user` (`id`, `Name`, `rank_id`, `supervisor_id`) VALUES
(1, 'john', 3, 2),
(2, 'james', 5, 1),
(3, 'geore', 4, 3),
(4, 'aby', 3, 2),
(5, 'john', 2, 1);

CREATE TABLE IF NOT EXISTS `rank` (
 `id` int(9) NOT NULL AUTO_INCREMENT,
 `rank_name` varchar(50) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
--
-- Dumping data for table `rank`
--
INSERT INTO `rank` (`id`, `rank_name`) VALUES
(1, 'president'),
(2, 'vice president'),
(3, 'prime minister'),
(4, 'cabinet minister'),
(5, 'minister');

expected result

I need result like below mentioned table

CREATE TABLE IF NOT EXISTS `result` (
 `id` int(7) NOT NULL,
 `name` varchar(90) NOT NULL,
 `rank` varchar(50) NOT NULL,
 `supervisor` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `result`
--
INSERT INTO `result` (`id`, `name`, `rank`, `supervisor`) VALUES
(4, 'aby', 'prime minister', 'vice president');

Answer 1

You have to join the user table to the rank table twice

SELECT u.id,u.Name,r1.rank_name Rank,r2.rank_name Supervisor
FROM user u
INNER JOIN rank r1 ON u.rank_id = r1.id
INNER JOIN rank r2 ON u.supervisor_id = r2.id
WHERE u.id = 4;

or

SELECT u.id,u.Name,r1.rank_name Rank,r2.rank_name Supervisor
FROM user u
INNER JOIN rank r1 ON u.rank_id = r1.id
INNER JOIN rank r2 ON u.supervisor_id = r2.id
WHERE u.Name = 'aby';

Give it a Try !!!

Answer 2

SELECT tbl_trip.trip_id, tbl_trip.location, tbl_member.gender 
FROM (tbl_trip) 
Left JOIN tbl_member ON tbl_member.trip_id = tbl_trip.trip_id

• RDFozz: It's not clear that this was an answer to this question. You're using different table names, and your query doesn't show how to pull data from two separate IDs to the same table.