Problem finding child field from primary field in mysql

Question

I have two tables as shown 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

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 that I want to get both nationality_id, country_idname of the country of the table, and for this they need to use LEFT JOIN query, so in this case I'm faced with a problem, because im receives the same name for both, if nationality_id, country_iddifferent from friend, since I can only join one table so that someone can help me solve this problem.

Answer 1

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

Sort of

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 ( n on). .

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