I have a table of projects (one row per project), then a table for risks and another for issues associated to each project (there can be any number of rows per project in these tables). I'd like to add two columns to the projects table that returns a number of 1-3 forming an overall risk and issue rating for the project, based on the ratings specified in the risks and issues tables.
Each risk and issue added to a project is given a rating of 1-3: 1=Green, 2=Yellow, 3=Red. If a red risk or issue exists on a project, I want the overall project risk/issue rating to be 3. Then if a yellow risk or issue exists and a red one doesn't, the overall rating should be 2. If no yellow or red ones exist, it should be 1.
Is it possible to perform a merge between these tables but have only the highest number returned? Any other ideas on the simplest way to achieve this would be appreciated.
