Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table named mip. The table has primary key id, a foreign key product_id and a column usage_type. product_id is a primary key id to a table named product.

It is one to many relationship from product to mip table (so a single product can have many mip).

I want to know all the products which have more than one mip rule with different usage type.

Could you please help me write the query ?

This is what I have written so far:

select count(product_id)
from mip, product
where = mip.product_id
group by product_id
having count( > 1)
share|improve this question
can you post some sample data and the expected result? or even better create a – bluefeet Sep 26 '12 at 19:47
up vote 0 down vote accepted


  • Write it as a INNER JOIN
  • Change having count( > 1) to having count( > 1
  • Try counting just mip table for counting

Here are those changes

from product INNER JOIN mip
ON = mip.product_id
group by,mip.usage_type having count( > 1

Give it a Try !!!

share|improve this answer
Thanks a lot for an immediate reply Rolando ! I have got the result until this part. But what I actually need is the number of products amongst these products which have more than one mip and different usage type – vinny Sep 26 '12 at 20:21
Which table has the usage type ??? – RolandoMySQLDBA Sep 26 '12 at 20:26
mip table has usage type ! – vinny Sep 26 '12 at 20:33
Updated my query – RolandoMySQLDBA Sep 26 '12 at 20:44
Thanks A lot ! It worked – vinny Sep 26 '12 at 21:34

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.