Databases
StackOverflow2013
Postsdbo
POSelect newest entry from a joined MySQL table
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelect newest entry from a joined MySQL table
    text
    copied!<p>I have stock quantity information in my database. <br>1 table, "stock", holds the productid (sku) along with the quantity and the filename from where it came.</p> <p>The other table, "stockfile", contains all the processed filenames along with dates.</p> <p>Now I need to get all the products with their latest stock quantity values.</p> <p>This gives me ALL the products multiple times with all their stock quantity (resulting in 300.000 records)</p> <blockquote> <p>SELECT stock.stockid, stock.sku, stock.quantity, stockfile.filename, stockfile.date<br> FROM stock<br> INNER JOIN stockfile ON stock.stockfileid = stockfile.stockfileid<br> ORDER BY <code>stock</code>.<code>sku</code> ASC</p> </blockquote> <p>I already tried this:</p> <blockquote> <p>SELECT * FROM stock<br> INNER JOIN stockfile ON stock.stockfileid = stockfile.stockfileid<br> GROUP BY sku<br> HAVING stockfile.date = MAX( stockfile.date )<br> ORDER BY <code>stock</code>.<code>sku</code> ASC</p> </blockquote> <p>But it did not work</p> <p><b>SHOW CREATE TABLE stock:</b></p> <blockquote> <p>CREATE TABLE <code>stock</code> (<br> <code>stockid</code> bigint(20) NOT NULL AUTO_INCREMENT,<br> <code>sku</code> char(25) NOT NULL,<br> <code>quantity</code> int(5) NOT NULL,<br> <code>creationdate</code> datetime NOT NULL,<br> <code>stockfileid</code> smallint(5) unsigned NOT NULL,<br> <code>touchdate</code> datetime NOT NULL,<br> PRIMARY KEY (<code>stockid</code>)<br> ) ENGINE=MyISAM AUTO_INCREMENT=315169 DEFAULT CHARSET=latin1</p> </blockquote> <p><b>SHOW CREATE TABLE stockfile:</b></p> <blockquote> <p>CREATE TABLE <code>stockfile</code> (<br> <code>stockfileid</code> smallint(5) unsigned NOT NULL AUTO_INCREMENT,<br> <code>filename</code> varchar(25) NOT NULL,<br> <code>creationdate</code> datetime DEFAULT NULL,<br> <code>touchdate</code> datetime DEFAULT NULL,<br> <code>date</code> datetime DEFAULT NULL,<br> <code>begindate</code> datetime DEFAULT NULL,<br> <code>enddate</code> datetime DEFAULT NULL,<br> PRIMARY KEY (<code>stockfileid</code>)<br> ) ENGINE=MyISAM AUTO_INCREMENT=265 DEFAULT CHARSET=latin1</p> </blockquote>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload