php - MySQL statement pulling too many rows
I have two data tables that I am querying using an Inner Join statement. one of the tables pulls daily company stock information and consequently has a row for each company for each day the market is open. The other table holds qualitative data about the company and only has one row per company.
I am trying to pull the most recent stock information and pair it with the qualitative company information in an HTML table. I have everything working except that it is still pulling every row of daily info for the company rather than the most recent.
Can someone assist with this query?:
$query = "SELECT daily_info.Day, daily_info.Prev_close, stocks.Symbol, stocks.Company, stocks.Description FROM daily_info INNER JOIN stocks ON daily_info.Symbol = stocks.Symbol ORDER BY daily_info.Day, stocks.Company LIMIT 43 "; Example: Table 1: Daily_info Day | Symbol | Company | Prev Close 06/15/14 | CRM | Salesforce | $52.34 06/15/14 | AMZN | Amazon | $342.65 06/16/15 | CRM | Salesforce | $55.24 06/16/14 | AMZN | Amazon | $349.64 Table 2: Stock Symbol | Company | Description CRM | Salesforce.com | This is a cloud based CRM company AMZN | Amazon.com | This is an ecommerce company Output: Company | Symbol | Prev Close | Description Amazon.com | AMZN | $349.64 | This is an ecommerce company Salesforce.com | CRM | $55.24 | This is a cloud based CRM company
I don't think MySQL supports LAG functions so you'll need to do a subquery to get the last date for each Symbol, then INNER join on that result. Something like this should work: