Jump to content

[SOLVED] Left Join and Grouping help


ameyemad

Recommended Posts

I've been having some problems with left join and grouping, the results are just not working properly.

 

For example... lets say I have 2 tables...

 

table1

----------

id

message

 

table2

----------

id

tid

message

time

 

 

Query...

$query="Select t1.id,t1.message,t2.id,t2.message from table1 t1 left join table2 t2 on t1.id=t2.tid order by time desc";

 

Now, if t2.tid can have multiple entries the same, how do you group them properly so that the entry that has the highest t2.time is chosen and nothing else? I've tried group by t2.tid but it doesn't work.

 

Thank you.

Link to comment
https://forums.phpfreaks.com/topic/177256-solved-left-join-and-grouping-help/
Share on other sites

SELECT
  t1.id,t1.message,t2.id,t2.message
FROM
  table1 AS t1
LEFT JOIN
(
  SELECT 
    t2a.id, t2a.tid, t2a.message, t2a.time
  FROM
    table2 AS t2a
  CROSS JOIN
    (
       SELECT tid, MAX(time) AS time FROM table2 GROUP BY tid 
    ) AS t2b
   USING(tid, time)
) AS t2
ON (t1.id=t2.tid)

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.