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 display books that are written, for example - Pupkin, in collaboration with someone else? Tell me how to act?

I suspect that it is necessary to select all of Pupkin’s books, to find someone else other than his author, and to withdraw all these books, excluding books where only Pupkin is.

Can this be done in one request? Where to dig?

thank

  • Right! Bring them to clean water! And then, you know, stuck to someone else's authorship! - Vlad from Moscow

1 answer 1

There are many ways to write such a request. For example:

 select bookId, bookTitle from books join indexes m2m using(bookID) join authors using(authorID) where authorName = 'Пупкин' and exists ( select 1 from indexes ao where ao.bookId = m2m.bookID and ao.authorID != m2m.authorID )
  • thank! checked, works) Please explain what the construct does "... and exists (select 1 from ..." - losew
  • And how, for example, select all Pupkin's co-authors in books from 1960 to 1980? - losew
  • exists is an operator that returns true if the subquery returns at least one row. Some data in the subquery to return meaningless, select какая-то константа often used option. All collaborators: select distinct authorID from indexes where bookid in (select bookId from indexes where authorId = :user_id) . Like so. Comprehensively no mood to write now. Add in places a pair of joins. - Small
  • I understand, thanks, here at the end, apparently, a typo? ... where authorId =: user_id) - losew