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
NumLevel are foreign keys that references to the other tables.
the primary keys are
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
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;