Compare database value to number of rows in another table

Question

Hello all.

For each item in the first table, there is a 'numberOf' field. The value of this field must have an identical number of rows in a related table. These are like reservation rows so multiple users can book the item at the same time. This syncronisation sometimes goes out and there are more rows than the 'numberOf' field variable, and vice versa.

So I want to display a table that outputs the 'numberOf' from the first table, and the amount of rows that correspond to it from the other table. They are linked by the Item ID. Hope this isn't too confusing. The query is output with a do while loop. Here is the query I have so far anyway:

$querySync = sprintf("SELECT 
                          COUNT(reserve_id), item_id, details, numberOf
                      FROM 
                          reservations     
                      JOIN 
                          items ON item_id = itemID_reserved        
                      WHERE 
                          itemID_reserved = 1 ");

So at the moment it counts the number of rows in the reservations table. It then joins the items table so I can display the description and numberOf etc. Of course at the moment it only outputs the item with ID 1. But I can't seem to get it to go though each item, check its numberOf, and compare it to the number of rows in reservations table.

The idea is to have it all on one column and at the end of the row print if it is out of sync etc. I then need to rebuild the rows in the reservations table to match the numberOf.

Sorry thats a long one!

Answer 1

SELECT  COUNT(reserve_id), item_id, details, numberOf,
        COUNT(reserve_id) > numberOf AS overbook
FROM    items
LEFT JOIN
        reservations
ON      itemID_reserved = item_id
GROUP BY
        item_id

Answer 2

It might be easier to just directly calculate which items are "out of sync":

select i.item_id
from reservations r JOIN items i on (i.item_id = r.itemID_reserved)
group by i.item_id
having count(r.itemID_reserved) > i.numberOf

I'm making some assumptions there about which tables have which fields, but it should be sufficiently illustrative.

Answer 3

Thanks for your replies everyone. I think it would make more sense to run a query to find out which items are out of sync. Thanks TML

This is what I have tried but just brings up one random row instead of just the ones with that are 'out'

SELECT items.item_id, items.details, items.numberOf
FROM reservations JOIN items ON (items.item_id = reservations.itemID_rsvd)
HAVING COUNT(reservations.itemID_rsvd) > items.numberOf

I think it's counting all of the itemID_reserved rows which is going to be bigger than the numberOf value. Any ideas?