Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 product.id = mip.product_id
group by product_id
having count(mip.id > 1)
share|improve this question
    
can you post some sample data and the expected result? or even better create a sqlfiddle.com? –  bluefeet Sep 26 '12 at 19:47

1 Answer 1

up vote 0 down vote accepted

Suggestions

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

Here are those changes

select product.id,mip.usage_type,count(mip.id)
from product INNER JOIN mip
ON product.id = mip.product_id
group by product.id,mip.usage_type having count(mip.id) > 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

 
discard

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.