SQL Help!!!

[jared717]

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.

[jbisono]

Try to repost your select statement between tags because I cannot even seen it.

[jared717]

Thanks, I just fixed the code

[pty]

You seem to be getting mixed up with your joins.

You need to do an inner join to get the winning bids. I'd create this as a view:

I just typed this from memory so sorry about the table/column names :P

create view winning bids as
select b1.* 
from bids b1 
inner join (
	select 	b2.item_id, 
			max(b2.amount) as amount 
	from bids b2 
	group by b2.item_id
) b2 
on b1.item_id = b2.item_id 
and b1.amount = b2.amount;

Then you can use a left join to join from items; the left join will mean that if an item doesn't have a winning bid it will show NULL.