php - MySQL statement pulling too many rows

340

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
883

Answer

Solution:

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:

 $query = "
    SELECT l.Company, l.Symbol, l.Prev_Close, r2.Description
    FROM Daily_info as l
    INNER JOIN
    (SELECT Symbol, MAX(Prev_close) as last_date FROM Daily_info GROUP BY Symbol) AS r
    ON (l.Symbol=r.Symbol AND l.Prev_close=r.last_date)
    INNER JOIN Stock as r2
    ON (l.Symbol=r2.Symbol)
    ";

People are also looking for solutions to the problem: ini - Allowing large file uploads in PHP (security)

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.