Search code examples
mysqlouter-jointable-alias

Subquery table-alias not being recognized in JOIN-ON/WHERE

I'm trying to lend some help to a hobbyist friend, using SQL Server experience to help with MySQL.

He's working on a game database. I wrote this query for him

SELECT ib.itemid, ii.realname as name, ib.stackSize,
       IFNULL(ah.price, '-') as price, ah.stock
    FROM item_basic ib
    LEFT OUTER JOIN item_info as ii ON ii.itemID = ib.itemID
    LEFT OUTER JOIN (SELECT price, COUNT(*) as stock
        FROM auction_house
        GROUP BY itemID) as AH on ii.itemID = AH.itemID -- erring line
    ORDER BY ii.realname

And a version that used assembled the subquery in the FROM cluse rather than a JOIN, but that was less troublesome than this version.

This gives the error Unknown column 'ah.itemID' in 'on clause'

If I change the erring line, incorrectly, to ... ii.itemID = ib.itemID, the query executes, but of course the results are incorrect (exponentially doubled in this case).

Here's an SQL Fiddle with sample data.

Solution

  • It's because you're not selecting itemID in your subquery. In addition, you're selecting the price column without aggregating by it. Try replacing

    LEFT OUTER JOIN (SELECT price, COUNT(*) as stock
        FROM auction_house
        GROUP BY itemID) as AH on ii.itemID = AH.itemID

    with

    LEFT OUTER JOIN (SELECT itemID, price, COUNT(*) as stock
        FROM auction_house
        GROUP BY itemID, price) as AH on ii.itemID = AH.itemID