Query to know which books students do not have, in SQL

Asked

Viewed 43 times

-6

Good, I have the following tables

Livro:
  ID_Livro PK
  Nome

Aluno:
  ID_Aluno PK
  Nome

RegistoLivroAluno:
  ID_Registo PK
  ID_Aluno   (chave extrangeira de Aluno)
  ID_Livro   (chave extrangeira de Livro)
)

I need to know in SQL, What is the Query that can see the Id_student and the Id_book, of those that are not associated, or see the books that a particular student does not have.

mysql sql sql-server mysqli sqlite
  • 4

    Have you tried anything? What was the result? Your question is really about Mysql, SQL Server, Mysqli and Sqlite? Please review the tags you used.

  • The NOT EXISTS clause can be of great help. If you want I can give an example in Postgresql.

2 answers

0

SELECT * 
FROM Livro
INNER JOIN RegistoLivroAluno ON RegistoLivroAluno.ID_Livro = Livro.ID_Livro
INNER JOIN Aluno ON RegistoLivroAluno.ID_Aluno = Aluno.ID_Aluno
WHERE Aluno.ID_Aluno = RegistoLivroAluno.ID_Aluno
AND Aluno.ID_Aluno =2

This is what I have done to show the books associated with the student, only that I want those that are not associated

0


Unregistered books:

SELECT * FROM Livro WHERE NOT EXISTS (SELECT * FROM RegistroLivroAluno WHERE RegistroLivroAluno.ID_Livro = Livro.ID_Livro);

Unregistered students:

SELECT * FROM Aluno WHERE NOT EXISTS (SELECT * FROM RegistroLivroAluno WHERE RegistroLivroAluno.ID_Aluno = Aluno.ID_Aluno);

Browser other questions tagged mysql sql sql-server mysqli sqlite

You are not signed in. Login or sign up in order to post.