homepage Welcome to WebmasterWorld Guest from 54.205.254.108
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
UNION in a subquery
noyearzero




msg:4081674
 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'"

do i need to structure things differently?

 

whoisgregg




msg:4083185
 10:41 pm on Feb 18, 2010 (gmt 0)

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




msg:4083972
 5:29 pm on Feb 20, 2010 (gmt 0)

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


Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved