sql - SQLite query structure. It might be a many to many relationship, I don't know -

ok, think getting in on head, please bear me!

i have simple sqlite table set such:

drop table if exists "game_authors"; create table "game_authors" ("key" integer primary key  autoincrement  not null  unique , "book_key" integer not null  default 0, "author_key" integer not null  default 0);  key | book | author ------------------- 1     1      1 2     2      5 3     2      6 4     3      2 5     4      5 6     4      6 7     5      10 8     6      11 9     9      14 10    10     9 11    11     4 12    22     4 

the search trying wrap head around finding book(s) author(s). example: want list of book_keys have author_key=5 , author_key=6. maybe it's easy , can't see in front of face, can't figure out. appreciated!

to book 2 authors can subquery:

select book_key game_authors author_key = 5   , book_key in (     select book_key     game_authors     author_key = 6     ); 

or inner join same table, linking 2 book_key

select a.book_key game_authors inner join game_authors b on a.book_key = b.book_key a.author_key = 5   , b.author_key = 6; 

you can check this fiddle see working