Msg#: 4081672 posted 11:13 pm on Feb 16, 2010 (gmt 0)
i have an 'items' table which has a field 'auction' which is the id of the current auction for that item.
there are two different types of auctions: live and silent. each have their own table.
then there are two additional tables for bids: 'live_bids', 'silent_bids'
as i want to be able to grab each item and display the current highest bid for that item, whether its from a live auction or silent auction.
heres what i'm going for
SELECT items.*, ( SELECT max_bid FROM ( ( SELECT MAX(amount) AS max_bid FROM silent_bids WHERE silent_id = items.auction ) UNION ( SELECT MAX(amount) AS max_bid FROM live_bids WHERE live_id = items.auction ) ) as dt1 ) AS high_bid FROM items
it keeps telling me "#1054 - Unknown column 'items.auction' in 'where clause'"