Welcome to WebmasterWorld Guest from 54.166.152.121

Forum Moderators: open

Message Too Old, No Replies

UNION in a subquery

     
11:13 pm on Feb 16, 2010 (gmt 0)

5+ Year Member



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?
10:41 pm on Feb 18, 2010 (gmt 0)

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
5:29 pm on Feb 20, 2010 (gmt 0)

10+ Year Member



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

 

Featured Threads

Hot Threads This Week

Hot Threads This Month