Help with Complicated SELECT query

I have this SELECT query:

SELECT Auctions.ID, Users.Balance, Users.FreeBids, 
      COUNT(CASE WHEN Bids.Burned=0 AND Auctions.Closed=0 THEN 1 END) AS 'ActiveBids',
      COUNT(CASE WHEN Bids.Burned=1 AN开发者_如何学GoD Auctions.Closed=0 THEN 1 END) AS 'BurnedBids' 
FROM (Users INNER JOIN Bids ON Users.ID=Bids.BidderID) 
  INNER JOIN Auctions 
   ON Bids.AuctionID=Auctions.ID 
WHERE Users.ID=@UserID 
GROUP BY Users.Balance, Users.FreeBids, Auctions.ID

My problam is that it returns no rows if the UserID cant be found on the Bids table.

I know it's something that has to do with my

(Users INNER JOIN Bids ON Users.ID=Bids.BidderID)

But i dont know how to make it return even if the user is no on the Bids table.

---

You're doing an INNER JOIN, which only returns rows if there are results on both sides of the join. To get what you want, change your WHERE clause like this:

Users LEFT JOIN Bids ON Users.ID=Bids.BidderID

You may also have to change your SELECT statement to handle Bids.Burned being NULL.

If you want to return rows even if there's no matching Auction, then you'll have to make some deeper changes to your query.

---

My problam is that it returns no rows if the UserID cant be found on the Bids table.

Then INNER JOIN Bids/Auctions should probably be left outer joins. The way you've written it, you're filtering users so that only those in bids and auctions appear.

---

Left join is the simple answer, but if you're worried about performance I'd consider re-writing it a little bit. For one thing, the order of the columns in the group by matters to performance (although it often doesn't change the results). Generally, you want to group by a column that's indexed first.

Also, it's possible to re-write this query to only have one group by, which will probably speed things up.

Try this out:

with UserBids as (
    select
        a.ID
    ,   b.BidderID
    ,   ActiveBids = count(case when b.Burned = 0 then 1 end)
    ,   BurnedBids = count(case when b.Burned = 0 then 1 end)
    from Bids b
     join Auctions a
       on a.ID = b.AuctionID
    where a.Closed = 0
    group by b.BidderID, a.AuctionID
)

select
    b.ID
,   u.Balance
,   u.FreeBids
,   b.ActiveBids
,   b.BurnedBids
from Users u
 left join UserBids b
   on b.BidderID = u.ID
where u.ID = @UserID;

If you're not familiar with the with UserBids as..., it's called a CTE (common table expression), and is basically a way to make a one-time use view, and a nice way to structure your queries.