MySQL: Problems limiting the result set before the HAVING clause

I'm stuck with retrieving data from a MySQL-database in an efficient way.

I have a table with a lot of items. Each item can have a status 1, 2 or 3. I want to select item with certain statusses, depending on links to these records in other tables.

Example:

• A table items with fields: id, name, status, ...
• A table bought with fields: itemId, userId
• A table rented with fields: itemId, userId

Say I want this for userId 123:

1. All the records that are bought, having status 1
2. All the records that are rented, having status 1 or 2
3. All the records that aren't bought or rented, having status 3

My select query now looks something like this:

SELECT
  i.id,
  i.name,
  i.status,
  // bought by this user?
  SUM(IF(b.userId = 123, 1, 0)) AS bought,
  // rented by this user?
  SUM(IF(r.userId = 1开发者_开发技巧23, 1, 0)) AS rented,

FROM items i

LEFT JOIN bought b
  ON b.itemId = i.id

LEFT JOIN rented r
  ON r.itemId = r.id

GROUP BY i.id

HAVING
  // bought and status 1
  (bought > 0 AND status = 1)
  // rented and status 1 or 2
  OR (rented > 0 AND status IN (1, 2)
  // not bought or rented and status 3
  OR (bougth = 0 AND rented = 0 AND status = 3)

ORDER BY i.name ASC

Question 1

Is the SUM part in the SELECT clause a good way to determine whether there are entries in another table linked to an item? Assuming there's only one entry per user, the sum will be 1 or 0, giving me the information I need. But it just seems.. weird somehow.

Question 2

Even though this works, there's one big problem: it basically retrieves all the items and then filters them using the HAVING clause. Since there are quite a few entries, the queries is way too slow this way. I'm trying to figure out how to fix this.

I first tried a WHERE clause.. but how?

...
WHERE
  // only items with status 1 if bought or rented
  (t.status = 1 AND (bought > 0 OR rented > 0))
  // only items with status 2 if rented
  OR (t.status = 2 AND rented > 0)
  // only items with status 3 if not bought or rented
  OR (t.status = 3 AND bought = 0 AND rented = 0)
...

But you can't use variables from the SELECT clause. And since there is no column rented or bought in the items table, this won't work.

I also tried using user-definable variables, but this didn't work either:

SELECT
  ...
  @bought := SUM(IF(b.userId = 123, 1, 0)) AS bought,
...
WHERE @bought = ... // does not work

Then I tried a sub-query, but I can't get it to use the main queries item id:

...
WHERE
  ...
  // only items with status 2 if rented
  OR (
    t.status = 2
    AND (
      SELECT COUNT(r2.userId)
      FROM rented r2
      WHERE r2.userId = 123
        AND r2.itemId = i.itemId // it doesn't recognize i.itemId
    ) > 0
  )
  ...

Any ideas? I'd also like to keep everything in a single query. This is just a stripped down example, but actual one is rather large. I'm sure I can split everything up and use various queries to collect everything separately, but that'll just add a lot more code and doesn't make the maintainability any easier.

Use two subqueries (one for the bought and one for the rented) and left join them to the user table in your main query.

EDIT: forgive my MySQL, it's been a while, but I had in mind something like:

select i.id, i.name, i.status, ifnull(b.TotalBought,0) AS ItemsBought, ifnull(r.TotalRented,0) AS ItemsRented
FROM items i 
LEFT JOIN (select itemid, COUNT(*) AS TotalBought FROM bought WHERE userid=123 GROUP BY itemid) AS b ON b.itemid=i.itemid
LEFT JOIN (select itemid, COUNT(*) AS TotalRented FROM rented WHERE userid=123  GROUP BY itemid) AS r ON r.itemid=i.itemid
WHERE (i.status=1 AND ifnull(b.TotalBought,0)>0)
OR (ifnull(r.TotalRented,0) >0 AND i.status in(1,2)
OR (ifnull(b.TotalBought,0)=0 AND ifnull(r.TotalRented,0) =0 AND i.status=3)
ORDER BY i.name ASC