How to denormalize many-to-many relation without nested select?

Question

I have the following schema:

• house has many students, student has one house
• gender has many students, student has one gender
• game has many students. student has many games

For instance, consider

create table sex (type text);
insert into sex values ("wizard");
insert into sex values ("witch");

create table house (nature text);
insert into house values ("Gryffindor");
insert into house values ("Hufflepuff");
insert into house values ("Ravenclaw");
insert into house values ("Slytherin");

create table students (
  sid integer primary key autoincrement, 
  name text, 
  fk_type integer, 
  fk_house integer, 
  constraint valid_sex foreign key (fk_type) references sex (ROWID), 
  constraint valid_grade foreign key (fk_house) references house (ROWID)
);
insert into students (name, fk_house, fk_type) values ("Harry", 1, 1);
insert into students (name, fk_house, fk_type) values ("Hermione", 1, 2);
insert into students (name, fk_house, fk_type) values ("Nymphadora", 2, 2);
insert into students (name, fk_house, fk_type) values ("Cedric", 2, 1);
insert into students (name, fk_house, fk_type) values ("Bridget", 2, 2);
insert into students (name, fk_house, fk_type) values ("Lupin", 2, 1);
insert into students (name, fk_house, fk_type) values ("Salazar", 4, 2);
insert into students (name, fk_house, fk_type) values ("Yurika", 4, 2);

create table games (game text);
insert into games values ("Quidditch");
insert into games values ("Aingingien");

create table likes (
  fk_student integer, 
  fk_game integer, 
  constraint strictM2M UNIQUE (fk_game, fk_student), 
  constraint valid_student foreign key (fk_student) references students (sid), 
  constraint valid_game foreign key (fk_game) references games (ROWID)
);

insert into likes values (1, 1);
insert into likes values (2, 1);
insert into likes values (2, 2);
insert into likes values (3, 1);
insert into likes values (4, 1);
insert into likes values (5, 1);
insert into likes values (5, 2);
insert into likes values (6, 1);
insert into likes values (6, 2);
insert into likes values (7, 1);
insert into likes values (8, 1);
insert into likes values (8, 2);

Now, I want to create a view that shows student roll number, name, gender, house, and games liked. I wrote a join query

select 
  s.sid, s.name, xy.type, h.nature, l.fk_game
from 
  students s 
  inner join sex xy on s.fk_type = xy.ROWID 
  inner join house h on s.fk_house = h.ROWID 
  inner join likes l on l.fk_student = s.sid 
order by 
  l.fk_game;

But I want to see game's real names not ROWIDs, of course I can replace l.fk_game by (select game from games where ROWID = l.fk_game) but is it possible to get the same result without nested select?

Answer 1

Nothing forbids you from adding another join, to games. Then you can add the games.game column in the SELECT list:

select 
  s.sid, s.name, xy.type, h.nature, g.game
from 
  students s 
  inner join sex xy on s.fk_type = xy.ROWID 
  inner join house h on s.fk_house = h.ROWID 
  inner join likes l on l.fk_student = s.sid 
  left join games g on g.ROWID = l.fk_game
order by 
  s.sid,
  l.fk_game;