The task is about a database of books, where authors can be authors of several books, and books can have several authors. There are three tables in the database:

books (bookID, bookTitle, bookAge); authors (authorID, authorName, authorAge); indexes (bookID, authorID)

How to choose all the authors of Pupkin in the books from 1960 to 1980?

Tell me how to act? Can this be done in one request? thank

  • Can you provide table structures? - Stanislav Grot
  • This type of table links has the name "many to many" - Dmitriy Gvozd
  • What is meant by the structure of the tables? I wrote above about three tables in the database: the table indexes connects the table with the names of books books with the table with authors authors, also in the latter there is the year of publication of the book (bookAge) and the age of the author (authorAge) - losew

2 answers 2

Without a structure and an example of a table it is difficult to understand, but try something like this:

 select distinct a2.authorName from indexes i1 join indexes i2 on i1.bookID = i2.bookID and i1.authorID <> i2.authorID join authors a1 on i1.authorID = a1.authorID join authors a2 on i2.authorID = a2.authorID join books b1 on i1.bookID = b1.bookID where a1.authorName = 'Пупкин' and b1.bookAge >= 1960 and b1.bookAge <= 1980

Test sqlfiddle .

  • Thanks, it works, but how does the base understand pseudonyms before defining them? in the first line "a2" is already used in the link, although it is defined only in the fifth line "join authors a2 ..." - losew
  • @losew because it first executes from , then where , and only after that select . Read more here . If you have been given the correct answer, do not forget to check it with a check mark. - Denis
 with cte as ( select * from indexes as i inner join authors as a using(authorID) inner join books as b using(bookID) ) select distinct authorName from cte where bookID in ( select bookID from cte where authorName = "Пупкин" and bookAge >= 1960 and bookAge <= 1980 ) and authorName <> "Пупкин";
  • I ask you to explain the construction with "with cte as (..." - losew
  • @losew is a well-known design by which you can find a manual in a few seconds . - Denis
  • @losew roughly speaking, the with construct forms a table with the name cte existing in memory until the end of the query. It is possible without it, but then join'y would have to be done twice, in the query and subquery. - Sergey Gornostaev