Max Value from a joined table

[gameshints]

Hello!  I'm working on a project where the database structure was already setup by somebody else... so I want to see if I can get the data I want without modifying the way data is stored too much.

 

I have a table:

 

"reports"

 

report_idtext

1text

2text

3text

 

And a table:

 

"report_updates"

 

update_idreport_idreplydatetime

11reply text2007-06-0612:22:34

21reply text2007-06-0115:34:22

33reply text2007-01-0122:11:11

 

Basically, I want a list of all the reports with the date of the latest reply.  So I tried this:

 

SELECT * , MAX( report_updates.date )
FROM reports, report_updates
WHERE reports.report_id = report_updates.report_id
GROUP BY reports.report_id

 

Which pretty much works, except it doesn't return report #2, because there aren't any replies for it.

 

Also, is there a way I can gnab the reply with the max date + time?  If I ask for the max(date) and max(time), they could not unnecessarily be from the same reply.  (it would return 2007-06-06 as the max date, and 15:34:22 as the time... which are not from the same report reply)

 

Any insight to either of my problems would be much appreciated.  Thanks!

[fenway]

Well, a LEFT JOIN will fix the report #2 missing problem.

 

If you want the specific reply, run the above query as a dervied table, and then join back to the original table with both these fields in the ON clause.

[gameshints]

Well, this corrected the missing #2 problem:

 

SELECT *, MAX(report_updates.date) FROM reports LEFT JOIN report_updates ON reports.report_id = report_updates.report_id GROUP BY reports.report_id

 

But it's not joining the report_update with the latest (max) date... instead it's returning the first report_update it finds where the report_id matches.

[fenway]

Well, this corrected the missing #2 problem:

 

SELECT *, MAX(report_updates.date) FROM reports LEFT JOIN report_updates ON reports.report_id = report_updates.report_id GROUP BY reports.report_id

 

But it's not joining the report_update with the latest (max) date... instead it's returning the first report_update it finds where the report_id matches.

 

That's right... as I said earlier, you'll have to pull the tuples out first, then match them.

[gameshints]

Would you be able to clarify what you mean by pulling them out as a derived table then combining them again?

 

Thanks,

[fenway]

Actually, you really should have a DATETIME field and that would solve all of the problems; alternatively, you can just make a fake datetime from both pieces combined.  Didn't realize you could "cheat" and avoid the derived table completely.