Selecting a column from a table depending on the count of another column from the same table

The title sounds complicated xd couldn't put it to words more clear, so I'll try with details:

There are 3 tables

  • PRODUCT(Num,Name)
  • LEVEL(Num,Name)
  • RISK(NumProduct,NumLevel)

NumProduct and NumLevel are foreign keys that references to the other tables. the primary keys are Num , Num and NumProduct.

So the question is: I want to show the levels of the risks where the number of products > 10, this means any level that is repeated 10 times at least in the risk table should be selected, and the result has to show level.num, and the number of products.

I hope you guys help me I'm struggling with this, thanks in advance

Edit: this was what I tried but only on paper xd not sure if its correct

FROM Level,
WHERE Level.Num = Risk.NumLevel
GROUP BY NumProduct
HAVING COUNT(NumProduct) >= 10

1 answer

  • answered 2018-01-11 21:19 mtr.web

    If I follow the question properly, this should do the trick:

    FROM level
    JOIN risk
      ON level.num = risk.numproduct
    GROUP BY level.num
    HAVING COUNT(*) >= 10;