problems with "complex" select

[DIABLER]

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

[r937]

there are several things that need to be corrected

first, your method of obtaining the highest bidder is incorrect -- it returns every user for each auction with each user's highest bid, and not the overall highest bid of the auction

second, you needed LEFT OUTER JOINs, to return auctions with no bids

finally, you posted in the MySQL forum when it is obviously a SQL Server problem

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
     , bb.bids AS '# of Bids'
     , bb.maxbid AS 'Current Bid'
     , uu.UserName AS 'Highest Bidder' 
  FROM Auctions a
INNER
  JOIN ProfileTable_1 p
    ON p.UserID = a.UserID
LEFT OUTER
  JOIN ( SELECT AuctionID
              , COUNT(*)       AS bids
              , MAX(BidAmount) AS maxbid
           FROM Bids
          WHERE Active <> 0
         GROUP
             BY AuctionID) AS bb
    ON bb.AuctionID = a.AuctionID
LEFT OUTER
  JOIN ( SELECT b.AuctionID
              , b.BidAmount
              , u.UserName
           FROM Bids AS b
         INNER
           JOIN aspnet_Users AS u
             ON b.UserID = u.UserID
          WHERE b.Active <> 0 ) AS uu
    ON uu.AuctionID = a.AuctionID
   AND uu.BidAmount = bb.maxbid 
 WHERE a.AuctionID = @AuctionID

[DIABLER]

i have just simple thing to say : I LOVE YOU! lol

it works like a charm on the first copy & paste not changes made.
i just need to figure out how

Thanks a lot for this.