CREATE TABLE users (
  id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username varchar(100) NOT NULL,
  rol varchar(100) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE subjects (
  subject_id int(11) NOT NULL AUTO_INCREMENT,
  subject text,
  PRIMARY KEY (subject_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1;


CREATE TABLE users_subjects (
  users_subjects_id int(11) NOT NULL AUTO_INCREMENT,
  user_id_fk int(11),
  subject_id_fk int(11),
  FOREIGN KEY(user_id_fk) REFERENCES users(id),
  FOREIGN KEY(subject_id_fk) REFERENCES subjects(subject_id),
  PRIMARY KEY (users_subjects_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1;

$sql = "SELECT *
FROM users
where rol = 'alumno'
and id in (
    select distinct u.id
    from users u, users_subjects us, subjects e
    where u.username='".$_SESSION['username']."'
    and us.user_id_fk=u.id
    and e.subject_id=us.subject_id_fk
);";


$sql = "SELECT *
FROM users u
JOIN users_subjects us ON (us.user_id_fk = u.id)
JOIN subjects s ON (s.subject_id = us.subject_id_fk)
WHERE u.username = '".$_SESSION['username']."'
AND u.rol = 'alumno'";