1

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)
1
  • can you post some sample data and the expected result? or even better create a sqlfiddle.com?
    – Taryn
    Sep 26, 2012 at 19:47

1 Answer 1

0

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 !!!

4
  • 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, 2012 at 20:21
  • Which table has the usage type ??? Sep 26, 2012 at 20:26
  • mip table has usage type !
    – vinny
    Sep 26, 2012 at 20:33
  • Updated my query Sep 26, 2012 at 20:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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