How do i write this complex SQl statement using values within results group as condition?
Posted on 2008-06-11
The simplified view of this issue: I have 2 tables (ALERT, ALERT_GROUP) which can be joined on alert_id
AlLERT has 2 fields (alert_id and name)
ALERT_GROUP has 3 fields (group_id, alert_id and priority)
Given an alert_id ($alid) I wish to return a list of other alert_ids that belong to the groups of which $alid is a member BUT only if they have a priority greater than or equal to $alid within each group.
Writing a query to do this without checking the priorities within groups is realtively trivial but requires external processing and a second SQL call to get the desired result. I am trying to avoid having to do any of the statement outside of the DB if possible.