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, level.name 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

SELECT
  Num,
  Name,
  COUNT(NumProduct)
FROM Level,
     Risk
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:

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