UNION in a subquery

[noyearzero]

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'"

do i need to structure things differently?

[whoisgregg]

You probably would be better served here with a single table for bids, and a column like "bid_type" with values of either live or silent.

[syber]

I don't think you need a UNION to do this:

SELECT items.*,  
 ( SELECT MAX(amount) 
  FROM silent_bids  
  WHERE silent_id = items.auction ) as max_silent, 
 ( SELECT MAX(amount) 
   FROM live_bids  
   WHERE live_id = items.auction ) as max_live 
 FROM items