SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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:


    Code:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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



    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •