Select max value from many to many tables

join;oracle

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 ?

Best Answer

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.

Related Solutions

MySQL – Get Column from Multiple Tables

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

MySQL update one table from another joined many-to-many

This works - I had the JOIN wrong

UPDATE `events` 
  INNER JOIN `plays_in_events` ON (`plays_in_events`.`EventID` = `events`.`EventID`)
  INNER JOIN `plays` ON (`plays_in_events`.`PlayID` = `plays`.`PlayID`)
  SET events.Name = plays.Play
WHERE 
  events.Name IS NULL and plays_in_events.PlayID = 1

and the earlier comments were quite right, but it took me a while to 'get it' - thanks for your help. I had just copied the JOIN lines from the working SELECT query, but when I looked again, I could finally see what the commentators meant about events appearing twice, and plays_in_events not appearing properly in the JOINs.

Best Answer

Related Solutions

MySQL – Get Column from Multiple Tables

MySQL update one table from another joined many-to-many

Related Question