I have a table from which I am selecting records based on a set of criteria. Another so many tables are left joined as part of the select and which contain additional data used by the criteria.
There is a field in the main table that contains a grouping ID which groups sets of records - all of the records in the group contain the same grouping ID but might not all satisfy the criteria.
I want to include in the select the records in any groups from which one or more records satisfy the criteria.
For example, if I have 5 records:
Record 1 satisfies the criteria and belongs to group A
Record 2 does not satisfy the criteria and belongs to group A
Record 3 satisfies the criteria and belongs to group B
Record 4 does not satisfy the criteria and belongs to group C
Record 5 satisfies the criteria and belongs to group D
I want the select to include records 1, 2, 3, and 5. Records 1, 3, and 5 are included because they satisfy the criteria. Record 2 is included because, even though it does not satisfy the criteria, it is part of group A and record 1 which does satisfy the criteria is also in group A.
Can I do this in one select statement? If not then what would be the most efficient way? Would this be made easier if the grouping ID was in a separate table?