Page 1 of 1

[SOLVED] MySQL: Get related record with largest value

Posted: Fri Apr 30, 2010 6:20 am
by batfastad
Hi everyone

I have 2 tables: companies and notes
They're related by a company_id field
Each company record has many related records in the notes table
The notes table has 3 fields: company_id, create_stamp, note.
The create_stamp is a unix timestamp stored as an unsigned integer (not MySQL timestamp).

I'm trying to list a bunch of records from the companies table but I'd also like to output the most recent note for each company.
Since I'm using unix timestamps the most recent related note is the one with the largest value of create_stamp
However I also want to sort the results by the create_stamp returned from the join in ascending order.

I hope I've explained that ok, here's what I've got so far:

Code: Select all

SELECT `companies`.`rep`, FROM_UNIXTIME(`notes_join`.`create_stamp`, '%e/%m/%y'), `notes_join`.`note`, `companies`.`company_id`, `companies`.`company_name` 
FROM `companies` 
LEFT JOIN (
SELECT `company_id`, `create_stamp`, `note` FROM `notes` ORDER BY `create_stamp` DESC LIMIT 0, 1
) AS `notes_join` USING(`company_id`)
WHERE `companies`.`rep` LIKE '%MBC%'
ORDER BY `notes_join`.`create_stamp`, `companies`.`company_name`
I don't know much about sub-queries or joining on derived tables like this but the query seems to run without any errors.
The query above returns the correct number of results from the companies table as specified by the where clause... WHERE `companies`.`rep` LIKE '%MBC%'
The problem with the query above is that it's not returning the columns from notes_join that I wanted... so I've almost certainly borked up the join query

Anyone see anything obvious I'm doing wrong here?
Or any other comments/suggestions?

Cheers, B

Re: MySQL: Get related record with largest value

Posted: Fri Apr 30, 2010 9:12 am
by mikosiko
if in addition to the fields that you are selecting from the companies table you want to show ONLY the note field from the notes table you can use a scalar sub-query... like

Code: Select all

SELECT `fields-from-companies-table`, (SELECT `note` FROM `notes` WHERE `notes`.`company_id` = `companies`.`company_id` ORDER BY `create_stamp` DESC LIMIT 0, 1)
FROM `companies` 
WHERE `companies`.`rep` LIKE "%MBC%"
ORDER BY `companies`.`company_name`

Re: MySQL: Get related record with largest value

Posted: Fri Apr 30, 2010 9:14 am
by Eran
It's hard to tell what's really going on with that query since the formatting is kind of messed up. I wrote about this issue a while ago on my blog, you are welcome to go over it - http://www.techfounder.net/2010/03/12/f ... ith-mysql/

Re: MySQL: Get related record with largest value

Posted: Fri Apr 30, 2010 10:53 am
by batfastad
Yeah strange on the formatting, I just typed the query up in Notepad++ and never seen that happen before

@mikosiko
Yeah unfortunately I do need to get other fields from the notes table

@pytrin
That article was excellent. Just what I needed, I now have a much better understanding of derived tables for this sort of query.

Great stuff! :lol:
Thanks for the help both of you

Cheers, B