# Mysql – Count in an other table and show only theese records in query which has more than they used in other table

ms accessMySQL

I have for example 2 tables:

1. used(us_id, code_id);
2. codes(code_id, amount)

I want something like this:

If for example I have 5 eggs, and egg code is 1, then if the used table have reached 5 of ID 1 then in a from, the egg won't show up as choseable. (listbox, which get it's source from codes table)

I'm using Mysql server and Access connected by odbc.

If I get it right something like:

select c.code_id
from codes c
on c.code_id = u.code_id
group by c.code_id, c.amount
having count(1) < c.amount;


should do (Not sure why MySQL requires c.amount in the group by clause for it to be used in having)

If you want to show the remaining amount:

select c.code_id, c.amount - count(1) as remains
from codes c
on c.code_id = u.code_id
group by c.code_id, c.amount
having count(1) < c.amount


I assumed here that amount is functionally dependent of code_id. Another option is to add a "fake" aggregate to c.amount:

select c.code_id, max(c.amount) - count(1) as remains
from codes c