0

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?

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.