0

i have next tables in may data base:
my data base

I want to select the book that has the max number of pages for each author. I did this :

select auteur.nomauteur, auteur.prenomauteur ,livre.Title, livre.nombrepages from auteur, livre, livreauteur
  where (livre.idlivre = livreauteur.idlivre and livreauteur.idauteur = auteur.idauteur and
            livre.NOMBREPAGES in (select max(livre.nombrepages) from auteur, livre, livreauteur
  where (livre.idlivre = livreauteur.idlivre and livreauteur.idauteur = auteur.idauteur)
            group by auteur.nomauteur, auteur.prenomauteur)) group by auteur.nomauteur, auteur.prenomauteur, livre.title, livre.NOMBREPAGES;

Correct answer:

answer for the fisrt sql query, it is correct

But the expression is too long, and i need to do this select with JOIN. I tried to do like this:

select auteur.NOMAUTEUR, auteur.PRENOMAUTEUR, livre.TITLE, max(livre.nombrepages) from auteur inner join livreauteur on auteur.idauteur = livreauteur.idauteur inner join livre on livre.idLivre = livreauteur.IDLIVRE group by  auteur.NOMAUTEUR, auteur.PRENOMAUTEUR, livre.TITLE;

Wrong answer, 2 extra rows for the same author: result for secod query, it is wrong

also I tried with:

select
        auteur.idauteur,
          (select top 1
            livre.idlivre
            from livreauteur
            inner join livre
            on livre.idlivre = livreauteur.idlivre
            where livreauteur.idauteur = auteur.idauteur
            order by livre.nombrepages desc )
            from auteur;

But it is wrong. How can I do the first select with JOIN ?

1 Answer 1

1

The question would have been a better candidate for stackoverflow.

Anyways, the problem with your SQL is that the GROUP BY should only be on the author name and not on author name + book title.

From the GROUP BY on author name, get the max(number_of_pages) and wrap it inside another SELECT to to get the books by that author that have that those number of pages.

You would also have to consider the possibility that multiple books can have the same number of pages and if that should result in all of them being returned or just 1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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