Count if a user has reached the borrwing limit

Question

I've setup a fiddle with tables and data here

I'm trying to write a single sql to check if user has reached the borrowing limit for each category.

Right now, it's done using severals sql statements called after each other.

But the way it goes is simple. memId and id come through a querystring.

$medId = $_POST['memId']; Using 1 for this example. This is the members Id.
$id = $_POST['id']; Using 4 for this example. This is the item being lent.

After that I do:

select id, holder from collection_db where id = 4 // We have a valid item

select borrowMax from collection_db where id = (holder from the previous select) and category = 10 //Result = 2. Category indicates its a label and not a borrowable item.

select count(borrowedId) from lendings where memId = 1 and holder = (holder from the 1st query) //He's borrowed 2, under 1, so cant borrow any more. User 2 may borrow however.

if (count => borrowMax) {echo 'Cannot borrow more.';} else {echo 'Added to'}

How can this be combined into a single sql or is it best left this way?

Answer 1

This seems to produce a correct result set:

SELECT col1.id, col1.holder, col2.borrowMax, count(lend.borrowedId) as `count`
FROM collection_db col1
  INNER JOIN collection_db col2
  ON col1.holder = col2.id
    INNER JOIN lendings lend
    ON col1.holder = lend.holder
WHERE col1.id = $id
AND col2.category = 10
AND lend.memId = $medId