0

I've used the following stackoverflow post to try and get a record with the latest date: Select info from table where row has max date

I wrote the following query:

SELECT AR_SQ.SYMBOL, AR_SQ.NAME, AR_SQ.LAST, FF_PS.SECTOR, FF_PS.SECTOR_TAGS, HP.LAST_10_DAYS, HP.YTD_PERF 
FROM AR_STOCK_QUOTE AR_SQ 
LEFT JOIN (
SELECT HP.SYMBOL, max(TRADE_DATE) as MaxDate 
FROM FF_HISTORICAL_STOCK_PRICE HP
GROUP BY HP.SYMBOL
) 
HP on HP.SYMBOL = AR_SQ.SYMBOL 
JOIN FF_PRIMARY_STOCK_TABLE FF_PS on FF_PS.SYMBOL = AR_SQ.SYMBOL

When I do this though, I get a error message saying the following:

Error Code: 1054. Unknown column 'HP.LAST_10_DAYS' in 'field list'

Why would this be the case since HP is defined and it's a field in that table?

Community
  • 1
  • 1
user1048676
  • 9,756
  • 26
  • 83
  • 120

1 Answers1

2

because you are doing a join on a select statement not the table "FF_HISTORICAL_STOCK_PRICE" you cannot use HP on the global select because HP is not defined there

SELECT AR_SQ.SYMBOL, AR_SQ.NAME, AR_SQ.LAST, FF_PS.SECTOR, FF_PS.SECTOR_TAGS, HP.LAST_10_DAYS, HP.YTD_PERF  #3- you cannot use HP here
FROM AR_STOCK_QUOTE AR_SQ 
LEFT JOIN (
# 1- you are not doing ajoin on the table FF_HISTORICAL_STOCK_PRICE HP
# 2- you can use HP on this select 
SELECT HP.SYMBOL, max(TRADE_DATE) as MaxDate 
FROM FF_HISTORICAL_STOCK_PRICE HP
GROUP BY HP.SYMBOL
) 
HP on HP.SYMBOL = AR_SQ.SYMBOL 
JOIN FF_PRIMARY_STOCK_TABLE FF_PS on FF_PS.SYMBOL = AR_SQ.SYMBOLenter code here

add the field to the select statement in the join part now they will exist on the result HP and try to use different alias for the select 

SELECT AR_SQ.SYMBOL, AR_SQ.NAME, AR_SQ.LAST, FF_PS.SECTOR, FF_PS.SECTOR_TAGS, RS.LAST_10_DAYS, RS.YTD_PERF 
FROM AR_STOCK_QUOTE AR_SQ 
LEFT JOIN (
# add the last_10_days to the fields of you select statement now you can select them
SELECT HP.SYMBOL, max(TRADE_DATE) as MaxDate ,HP.LAST_10_DAYS, HP.YTD_PERF 
FROM FF_HISTORICAL_STOCK_PRICE HP
GROUP BY HP.SYMBOL
) RS on RS.SYMBOL = AR_SQ.SYMBOL  #HP is a result of select statement 
JOIN FF_PRIMARY_STOCK_TABLE FF_PS on FF_PS.SYMBOL = AR_SQ.SYMBOL
Charif DZ
  • 14,415
  • 3
  • 21
  • 40
  • How would I do this correctly then to access the values in that table? – user1048676 Jul 19 '16 at 20:01
  • I always try to use different aliases for values inside of a SELECT statement and when JOINing the SELECT statement for just this reason. SQL queries are already confusing/ convoluted enough. – RIanGillis Jul 19 '16 at 20:01
  • You have to include the LAST_10_DAYS column when doing the SELECT inside of the LEFT JOIN (as well as include it in the GROUP BY or use it in an aggregate function). – RIanGillis Jul 19 '16 at 20:04
  • ok i edited the answer with some comment how that you understand i have a poor english sorry – Charif DZ Jul 19 '16 at 20:06