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

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`?

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;
``````