-1

I have a sql request like that :

SELECT *, MAX(dateFin)
FROM t1 LEFT JOIN
     t2
     ON t1.id = t2.idUtl
GROUP BY t2.dateFin
ORDER BY t1.dateCreation
limit 0,10

I have this tables :

t1                         t2   
id   dateCreation          id   idUtl   dateFin
1    2015-02-25            1    1       2015-01-10
2    2015-02-22            2    1       2014-09-12
3    2015-06-20            3    3       2014-08-23

So with my request i have this result:

t1.id   dateCreation   t2.id   idUtl   dateFin
3       2015-06-20     3       3       2014-08-23
1       2015-01-10     1       1       2015-01-10

My problem is that i also want this line

t1.id   dateCreation   t2.id   idUtl   dateFin
2       2015-02-22     NULL    NULL    NULL

I try something with another SELECT in my request but it didn't work and i don't think it's a really good idea.

Wibbler
  • 1,020
  • 9
  • 17
Kvasir
  • 1,197
  • 4
  • 17
  • 31
  • 2
    Please tag your question with the database you are using. The `limit` suggests MySQL. – Gordon Linoff Jul 29 '15 at 15:19
  • 1
    Because you're doing a left join the values that aren't matched will only be associated with t1. FULL OUTER JOIN is what you're looking for.If MySQL check out this link http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql – FirebladeDan Jul 29 '15 at 15:22
  • @FirebladeDan the row he's missing is part of t1 though... – Alex Jul 29 '15 at 15:30
  • @Alex - No it's t2. The id's don't match so t2 is not returned – FirebladeDan Jul 29 '15 at 15:32
  • @FirebladeDan correct, but neither is t1 in his example. A LEFT JOIN will return all rows in the left table (t1), so something else is removing row t1.id=2 from the result set. – Alex Jul 29 '15 at 15:34

2 Answers2

0

Is this what you want?

SELECT t1.*, MAX(t2.dateFin)
FROM t1 LEFT JOIN
     t2
     ON t1.id = t2.idUtl
GROUP BY t1.id
ORDER BY t1.dateCreation
limit 0, 10;

I think the problem is your group by clause.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I find the solution :

SELECT  * from t1
left JOIN t2 ON t1.id = t2.idUtl
and date_fin=(select  MAX(t2.date_fin) from t2 where t1.id = t2.id_candidat )
ORDER BY t1.dateCreation
limit 0,10
Kvasir
  • 1,197
  • 4
  • 17
  • 31