trouble in find child field from primary field in mysql

Question

I have two table like below

CREATE TABLE IF NOT EXISTS `countries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=196 ;

ANd ANother one

CREATE TABLE IF NOT EXISTS `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `admission_no` varchar(255) DEFAULT NULL,
  `nationality_id` int(11) DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `is_deleted` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `admission_no` (`admission_no`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin

1 AUTO_INCREMENT=2 ;

So the problem is i want fetch both nationality_id,country_id name from countries table for this im have to use LEFT JOIN query so in this case i am facing problem as im getting same name for both if nationality_id,country_id are different as i can only join on one table only so could someone plz help me to solve this.

Answer 1

If I understand you correctly, you can achieve this by LEFT JOINING the same table twice, using aliases.

Something like

SELECT *
FROM students s LEF TJOIN
countries c ON s.country_id = c.id LEFT JOIN
countries n ON s.nationality_id = n.id

Answer 2

@astander there is a little bug in your query (second alias for countries n is not used in on statement). here is a correct statement.

select  s.Id, cNationality.Name, cCountry.Name
from    Students as s
left outer join Countries as cNationality on cNationality.Id = s.Nationality_id
left outer join Countries as cCountry on cCountry.Id = s.Country_id