How to denormalize many-to-many relation without nested select? - Database Administrators Stack Exchange most recent 30 from dba.stackexchange.com 2019-09-19T13:38:08Z https://dba.stackexchange.com/feeds/question/229935 https://creativecommons.org/licenses/by-sa/4.0/rdf https://dba.stackexchange.com/q/229935 0 How to denormalize many-to-many relation without nested select? RinkyPinku https://dba.stackexchange.com/users/61248 2019-02-16T12:08:56Z 2019-02-16T13:43:31Z <p>I have the following schema:</p> <ul> <li>house has many students, student has one house</li> <li>gender has many students, student has one gender</li> <li>game has many students. student has many games</li> </ul> <p>For instance, consider</p> <pre><code>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); </code></pre> <p>Now, I want to create a view that shows student roll number, name, gender, house, and games liked. I wrote a join query</p> <pre><code>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; </code></pre> <p>But I want to see game's real names not ROWIDs, of course I can replace <code>l.fk_game</code> by <code>(select game from games where ROWID = l.fk_game)</code> but is it possible to get the same result without nested <code>select</code>?</p> https://dba.stackexchange.com/questions/229935/-/229938#229938 1 Answer by ypercubeᵀᴹ for How to denormalize many-to-many relation without nested select? ypercubeᵀᴹ https://dba.stackexchange.com/users/993 2019-02-16T12:27:43Z 2019-02-16T12:27:43Z <p>Nothing forbids you from adding another join, to <code>games</code>. Then you can add the <code>games.game</code> column in the <code>SELECT</code> list:</p> <pre><code>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; </code></pre>