How to join the result of a request to another table (to add column)

Question

Edit

I have two tables : etape and etapex.

I need to find the maximum idProjet for each idEtape (in table etapex), and there link the idEtape and idProjet to 'nomEtapeandlivrable` from the table etape.

So I tried two different things :

SELECT etapexprojet.idEtape, idProjet, nometape, livrable
FROM etape, etapexprojet
WHERE etapexprojet.idetape = (SELECT MAX(etape.idetape) FROM etapexprojet )

Unfortunately, this is what I get :

Here's my other "solution":

The first step is to find the max value (I don't want to use group by) :

SELECT EX.idEtape
FROM etapexprojet EX                   
LEFT JOIN etapexprojet EX2       
  ON EX.idprojet = EX2.idprojet 
  AND EX.idetape < EX2.idetape
WHERE EX2.idetape IS NULL

But now I'm stuck, and I don't get how to join what I get from that first request to the table etape.

At the end, it should give me something like :

But with the columns nomEtape and livrable as well...

Thanks in advance for your help !

Answer 1

There are various solution to this greatest-n-per-group problem.

One simple method is to start from this aggregate query that gives you the maximum idEtape per IdProjet:

select idProjet, max(idEtape) idEtape from etapex group by idProjet

Then you can simply join this with the original table:

select e.*, p.idProjet
from etape e
inner join (
    select idProjet, max(idEtape) idEtape from etapex group by idProjet
) p on p.idEtape = e.idEtape

Answer 2

You can just JOIN the etape table to the results of your first query:

SELECT EX.idProjet, E.*
FROM etape E
JOIN etapexprojet EX ON EX.idEtape = E.idEtape              
LEFT JOIN etapexprojet EX2       
  ON EX.idprojet = EX2.idprojet 
  AND EX.idetape < EX2.idetape
WHERE EX2.idetape IS NULL
ORDER BY idProjet

Demo on SQLFiddle

Answer 3

Try this:

SELECT ET.idEtape, ETX.idProjet, ET.nomEtape, ET.livrable
FROM etape  ET               
LEFT JOIN etapexprojet ETX ON ET.idEtape = ETX.idEtape 
WHERE ETX.idEtape = (SELECT MAX(idEtape) FROM etapexprojet WHERE idProjet = ETX.idProjet);