Complicated SELECT statement: display records from multiple table

Databases
1

Following are the two tables i have:

i need to write a mysql statement that will do followings:

  • list the latest updated record of each company i.e. single record per company
  • it should only list todays/current date record

interms of sorting:

  • highest stock value with latest updated should be displayed on top.
  • if both company has same stock value then the one who updated first should go on top. (i.e. latest updated should be on second)

i did something like this but it displays multiple records for each company:

SELECT * from tblcompany, tblstock
WHERE tblcompany.comid = tblstock.comid
AND tblstock.updated >= CURRENT_DATE()
ORDER BY tblstock.stock DESC, tblstock.updated ASC

i will be grateful if someone can help me please.

2

Have you thought about adding a column to the tblcompany table which records the last entry for that company in the tblstock table.

3

Can’t see that pic (blocked by proxy I think), but from your description it sounds like a “the row having the groupwise max” problem.
Check out this thread: http://www.sitepoint.com/forums/mysql-182/collating-data-three-tables-763414.html