CODEWITHSUNDEEP
mysql
user1048676
user1048676

Reputation: 10066

Unknown Column when joining tables and using a subquery

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?

Upvotes: 0

Views: 420

Answers (1)

Charif DZ
Charif DZ

Reputation: 14751

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

Upvotes: 2

Related Questions