Results 1 to 3 of 3
Thread: problems with "complex" select
Dec 29, 2008, 13:34 #1
- Join Date
- Dec 2008
- 0 Post(s)
- 0 Thread(s)
problems with "complex" select
Hello , i'm new to this forum and SQL
so i get stuck with every simple thing
anyways i have a DB with 3 tables , Profile , Auctions , Bids
i want to select data from all 3 of them in one shot with that stored procedure below.
The problem is that when there are none BIDS made yet the Select returns nothing.
What i want is that even if there are no bids yet the first statement will fire and return
the values and just place null values on the empty fields (highest bidder , current bid , # of bids)
i looked it up here and used the active <> 0 and it didn't help. yet.
here is the procedure:
ALTER PROCEDURE dbo.wcbs_auction_summery ( @AuctionID int ) AS SELECT x.*, y.* , z.* FROM (SELECT a.AuctionTitle , a.StartingPrice as 'Starting Bid' , a.BuyoutAmount as 'Buyout Price' , a.DatePublished ,datediff(day , getdate() , a.DateOfExpire) as 'Time till end' , a.DateOfExpire , p.City , p.Country FROM Auctions a INNER JOIN ProfileTable_1 p ON a.UserID = p.UserID WHERE a.AuctionID = @AuctionID) x , (SELECT u.UserName as 'Highest Bidder' , max(b.BidAmount)as 'Current Bid' FROM Bids b INNER JOIN aspnet_Users u ON b.UserID = u.UserID WHERE b.AuctionID = @AuctionID AND b.Active <> 0 GROUP BY u.UserName) y , (SELECT count(*) as '# of Bids' FROM Bids WHERE AuctionID = @AuctionID AND Active <> 0 ) z