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.

Best Answer

If I get it right something like:

select c.code_id
from codes c
join used u
    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
join used u
    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
join used u
    on c.code_id = u.code_id
group by c.code_id
having count(1) < max(c.amount);

There is only one unique amount per code_id so the purpose of max is to "legalize" the group by.

SQL92 demanded that all columns in the select beside aggregates where included in the group by clause. SQL99 loosened this restriction and only requires that all columns in the select clause is functionally dependent of the group by.

I haven't tested it my self, but I hear that the latest version of MySQL (or was it MariaDB?) implements it this way. I've also heard that recent versions of postgreSQL does it that way.

Related Question