SELECT biggest row from a LEFT JOIN

1
votes

I have a SQL query that LEFT JOINs a table into it. This causes rows from the main table to be duplicated but with different rows from the JOINed table. How do I only select the rows with the highest date from the JOINed table.

Here's an example (this is the result from my query):

ID    Message    Date
---------------------------
0     Hi         2011-01-01
0     Bye        2011-02-05
0     Hello      2011-04-20
1     Test       2010-12-31
1     Testing    2010-11-15
2     Something  2010-12-12
2     Nothing    2011-01-01
2     Yes        2010-02-05
3     Cool       NULL

I want one row per ID, the row with the highest ID.

ID    Message    Date
---------------------------
0     Hello      2011-04-20
1     Test       2010-12-31
2     Nothing    2011-01-01
3     Cool       NULL

My current query is something like this (I just kinda made this up, but it's similar to the real one):

SELECT t1.ID, t2.Message, t2.Date
FROM t1
LEFT JOIN (
  SELECT t3.ID, t3.message, t3.Date
  FROM t3
  LEFT JOIN t4 ON t4.userID = 12 AND t3.ID = t4.ID
  WHERE t4.color = 'blue'
) AS t2
ON t1.ID = t2.ID
WHERE t1.userID = 12

I guess I could use PHP and loop through the results and pick out the ones I want, but can I have MySQL do that for me?

EDIT: Sorry, my 1st example was way wrong, this is more like what I want.

EDIT 2: I tried using GROUP BY and MAX, but I think I'm doing something wrong.

I tried:

SELECT t1.ID, t2.Message, MAX(t2.Date)
FROM t1
LEFT JOIN (
  SELECT t3.ID, t3.message, t3.Date
  FROM t3
  LEFT JOIN t4 ON t4.userID = 12 AND t3.ID = t4.ID
  WHERE t4.color = 'blue'
) AS t2
ON t1.ID = t2.ID
WHERE t1.userID = 12
GROUP BY t1.ID

But, that gave me:

ID    Message    Date
---------------------------
0     Hi         2011-04-20
1     Test       2010-12-31
2     Something  2011-01-01
3     Cool       NULL

How do I get the Message associated with the highest date.

2
phpmysqljoinleft-join
Inside inner query, put a group by on Id and select max date .... - Nitin Midha
It's hard for me to follow the example but it looks like you're along the right lines. Might make for a longer post but could you put together some table structures/sample data? - James C
@Rocket - Is t3.Id the primary key of t3 or is that purely a foreign key to the t1 table? - Thomas
If I am reading this query correctly, so for 0 id in T1 table, we have 3 different messages? - Nitin Midha
@Rocket - Yes, but it is not intuitive that a column called ID is a foreign key and not a primary key. My first thought with that scenario is that there is a 1:1 relationship between t1 and t3 as opposed to a 1:Many. - Thomas

2 Answers

1
votes

Like this: 

SELECT 
   t1.ID, 
   t1.Message, 
   MAX(t2.Date) as [Date]
FROM t1
  LEFT JOIN (
    SELECT t3.ID, t3.Date
    FROM t3
    LEFT JOIN t4 ON t4.userID = 12 AND t3.ID = t4.ID
    WHERE t3.color = 'blue'
 ) AS t2
 ON t1.ID = t2.ID
 WHERE t1.userID = 12
 GROUP BY t1.ID, t1.Message

You can use GROUP BY to group on certain values with the restriction that you have to group on all the values in the select list, unless it is an aggregate function, like MAX.

4
votes
Select t1.id, t1.Message, t3.date
From t1
    Left Join t3
        On t3.id = t1.id
            And t3.id = (
                        Select Max( t3_1.Id )
                        From t3 As t3_1
                        Where t3_1.id = t3.id
                        Having Max( t3_1.date ) = t3.date
                        )
Where t1.userID = 12

As far as I can tell, the join to t4 plays no part in the query whatsoever. It doesn't filter the results nor is anything from the t4 table displayed in the Select clause. In addition, I have assumed that the column t3.id is actually a foreign key to the t1 table and not the primary key. If it is the primary key, then the other filter for max date is not necessary.

Update given question revision

By adding criteria to the Where clause on t4, you have effectively converted it to an inner join. Still, one solution is:

Select t1.id, t3.Message, t3.date
From t1
    Left Join t3
        On t3.id = t1.id
            And t3.id = (
                        Select Max( t3_1.Id )
                        From t3 As t3_1
                            Join t4
                                On t4.id = t3_1.id
                        Where t4.color = 'blue'
                            And t3_1.id = t3.id
                        Having Max( t3_1.date ) = t3.date
                        )
Where t1.userID = 12