I have two tables
- user
- 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');
Best Answer
You have to join the user table to the rank table twice
or
Give it a Try !!!