I am having trouble with one thing on my project. I have already created the following table and inserted all of the information into it.
Items
ItemID Description BidFloor
100 1943 Monopoly game 80
101 1972 Ford Pinto Station Wagon 80
102 1.5 L 1921 Chateau Neuf du Pape 1,000
103 Ruby Slipper from Wizard of Oz 50,000
Bidders
BidderID LastName FirstName CreditLimit SSNO
1 Jones Rick 1,000 111-22-1111
2 Morris Gary 2,000,000 222-11-1111
3 Sussman David 25,000 333-33-8777
Bids
BidNumber ItemID BidderID Amount BidChannel
1 102 2 1,000 Phone
2 100 1 81 In Person
3 102 3 1,100 In Person
4 102 2 1,500 Phone
5 100 3 100 In Person
6 101 1 81 In Person
Now the problem that I am having is this. Here is the next set of instructions:
Write an SQL script which produces a query showing every single itemID, Description, Bidfloor, winning bid amount, winningBidNumber, winning bidder last name, and winning bidder first name. Your list should include items that were not bid on and should be in order from the lowest winning bid price to the highest winning bid price. Name your script winningbids.sql
I have it working except I don't know how to get the items that no one bids on. Heres my code for this part:
SELECT Items.itemID, description, bidfloor, amount as WinningBid, bidnumber, lastname, firstname
FROM Items FULL OUTER JOIN (SELECT Bids.ItemID, max(amount) as WinningBid
FROM Bids
GROUP BY Bids.ItemID) newTable
ON Items.ItemID = newTable.ItemID
FULL OUTER JOIN Bids ON Items.ItemID = Bids.ItemID
FULL OUTER JOIN Bidders ON Bidders.BidderID = Bids.BidderID
WHERE amount = newTable.WinningBid
ORDER BY amount;Any advice is greatly appreciated.
