LEFT JOIN with LIMIT

Question

I have two tables A and A_LOG

The relation is N A_LOG to 1 A

The A table is: a_id, slogan

The A_LOG table is: a_log_id, a_id, data

Inside the A table i have:

a_id | slogan
   1 | bla
   2 | bla bla
   3 | bla bla bla
   4 | bla bla bla bla

Inside the A_LOG table I have:

a_log_id | a_id | data
       1 |   1  | 2012-01-01
       2 |   1  | 2013-01-01
       3 |   3  | 2012-02-01
       4 |   3  | 2013-02-01

When I do the left join I get

 a_id | slogan              | data
    1 | bla                 | 2012-01-01
    1 | bla                 | 2013-01-01
    2 | bla bla             | NULL
    3 | bla bla bla         | 2012-02-01
    3 | bla bla bla         | 2013-02-01
    4 | bla bla bla bla     | NULL

What I want:

     a_id | slogan              | data
        1 | bla                 | 2013-01-01
        2 | bla bla             | NULL
        3 | bla bla bla         | 2013-02-01
        4 | bla bla bla bla     | NULL

I'm using a simple left join, I also tried to do a left join (select from A_LOG order by data DESC LIMIT1), but that limited the amount of joins it made.

I tried to make myself clear with an abstract example, many of the others I saw were confusing since they were using personal environments. My real situation has a lot of others joins and stuff, but this is the problem and I would like to isolate and solve.

Answer 1

Try this

SELECT A.a_id, A.slogan, max(AL.data) data
FROM A
LEFT OUTER JOIN A_LOG AL ON A.a_id = AL.a_id
GROUP BY A.a_id, A.slogan