1

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.

Community
  • 1
  • 1
Regular Jo
  • 5,190
  • 3
  • 25
  • 47

2 Answers2

2

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
Josh Eller
  • 2,045
  • 6
  • 11
1

There is no AH.itemID, you can only access the results of a subquery.

Uueerdo
  • 15,723
  • 1
  • 16
  • 21