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
SELECT x.*, y.* , z.*
(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'
WHERE AuctionID = @AuctionID
AND Active <> 0 ) z