subquery within a SELECT statement, what is wrong?

[johnseito]

What is wrong with this code? I am trying to get the seller name and buyer's name.
I can't do that, so logically the only thing that makes sense is to have a sub-query within a select statement. Thanks, please let me know.

SELECT bid.bidder, user.uname, 
(SELECT user.uname FROM bid INNER JOIN user ON bid.seller = user.uid) as [SELLER's name], 
bid.ITEMiid, bid.STARTtime, bid.BIDtime, bid.BIDprice

FROM user INNER JOIN bid ON bid.bidder = user.uid

I got an error message, at most one record can be returned by this sub-query.

So let me explain, the user name is on the user table. and the seller and buy both are on the bid table, off course seller and buyer are always will be different,
so I am trying to get the seller's name and buyer's name, and it looks like if I do a join, I can only either get the seller or the buyer's name and not both.

So what I did is create a sub-query within a SELECT of another separate join but the error is " at most one record can be returned by this sub-query."

[June7]

I think you need to include the user table in the query twice, one each joined to bidder and seller fields. Use the query builder and drag the user table into the builder window twice, create a link from each.

[johnseito]

Ok thanks. :-)

That works, although I don't like to use builder window to construct SQL because I think it can make you not a better SQL developer
but since I haven't seen a code similar I'd rely on it and now I see how it is written, hope I'd to be able to write it out on my own next time.

SELECT bid.bidder, user.uname, user_1.uname, bid.iid, bid.STARTtime, bid.BIDtime, bid.BIDprice, auction.enddate, auction.endtime
FROM ([user] INNER JOIN bid ON user.uid = bid.bidder) INNER JOIN [USER] AS USER_1 ON bid.seller = USER_1.uid