[SOLVED] Left Join and Grouping help

[ameyemad]

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.

[ameyemad]

Any help please?

[avvllvva]

what is your query result... is it grouping ??

I think u want to group by tid with highest time .. then wat abut this

group by t2.tid order by t2.time desc

[ameyemad]

sorry but t2.tid doesn't work as i stated in first post

[Mchl]

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)

[ameyemad]

I think that's really close, but I get this error:

 

Unknown column 't2.tid' in 'on clause'

 

any ideas?

[ameyemad]

any help please?

[kickstart]

Hi

 

I have just set up some dummy tables to double check and the SQL posted my Mchl works fine.

 

Can you confirm the structures of your 2 tables.

 

All the best

 

Keith

[ameyemad]

thanks for the help, i worked out the problem i was having.

 

thanks again!