SitePoint Sponsor |
|
User Tag List
Results 1 to 3 of 3
Thread: problems with "complex" select
-
Dec 29, 2008, 14:34 #1
- Join Date
- Dec 2008
- Posts
- 5
- Mentioned
- 0 Post(s)
- Tagged
- 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:
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
-
Dec 29, 2008, 16:42 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- 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
-
Dec 30, 2008, 01:08 #3
- 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