CODEWITHSUNDEEP
sql
KageArarshi
KageArarshi

Reputation: 21

SQL Query- Working out right result

Hi all got a sql query that I just can't figure out for class, and was wondering I could get some help. Here is the question:

Display a “Winning Members List”: a list of members who have won a bid together with the item name, item value, and the winning bid amount for that item. my code is only displaying the highest bid out of all of them instead of each members highest bid

And the tables:

Member (username, lastName, firstName, title, address, city, postcode, country,
     phoneBH, phoneAH, faxNumber, email, registrationDate, password, isBuyer,
     isSeller)    
Item (itemNumber, itemName, itemDescription, itemValue, itemLocation,
     categoryID, sellerUsername)       
Auction (auctionNumber, currency, startDateTime, endDateTime, shippingTerms,
     startBidAmount, reserveAmount, bidIncrementAmount, noOfItems, itemSold,
     itemNumber feedbackDateAndTime, rating, comments, paymentDate, paymentid)    
Bid (bidderUsername, auctionNumber, bidDateTime,bidAmount)            

SELECT B.bidderUsername, I.itemName, I.itemValue, B.bidAmount, A.auctionNumber        
FROM Item I, Auction A, Bid B        
WHERE A.auctionNumber = B.auctionNumber        
AND I.itemNumber = A.itemNumber        
AND B.bidAmount >= A.reserveAmount        
AND A.endDateTime < SYSDATE        
AND B.bidAmount = (SELECT max(B.bidAmount)                          
FROM dbf12.Bid B, dbf12.Auction A                           
WHERE B.auctionNumber = A.auctionNumber)

for the code and the tables you may need to select the code/tables and push ctrl-k

Upvotes: 0

Views: 91

Answers (1)

KageArarshi
KageArarshi

Reputation: 21

I fixed it up. I was refferring to the wrong table in my sub query. Here is the correct code

SELECT B.bidderUsername, I.itemName, I.itemValue, B.bidAmount, A.auctionNumber
FROM dbf12.Item I, dbf12.Auction A, dbf12.Bid B
WHERE A.auctionNumber = B.auctionNumber
AND I.itemNumber = A.itemNumber
AND B.bidAmount >= A.reserveAmount
AND A.endDateTime < SYSDATE
AND B.bidAmount = (SELECT max(B.bidAmount)
                 FROM dbf12.Bid B
           WHERE B.auctionNumber = A.auctionNumber);

Upvotes: 1

Related Questions