How do I return a set of rows when a condition is true on one or more rows within the set?
How do I return a set of multiple rows when a condition is true on one or more rows within the set?
RECORD VERSION INDICATOR AREA 1234 01 A CA 1234 02 B CA 1234 03 C CA 5678 01 D CO 5678 02 L CO 5678 03 F CO
For example, I would like to return all rows within a record set if a B indicator is present within the set.
I can query for that condition, but it simply returns the one row where the condition is true.
SELECT record, version, indicator, area FROM vista WHERE indicator = 'B';
RECORD VERSION INDICATOR AREA 1234 01 A CA 1234 02 B CA 1234 03 C CA
You can use
EXISTSand a correlated subquery that checks for a record with a
SELECT v1.record, v1.version, v1.indicator, v1.area FROM vista v1 WHERE EXISTS (SELECT * FROM vista v2 WHERE v2.record = v1.record AND v2.indicator = 'B');
Since the common field is
Area, you want two subsets that match on that field.
SELECT va.record, va.version, va.indicator, va.area FROM vista va WHERE va.Area = 'CA' AND EXISTS ( SELECT * FROM vista WHERE Area = va.Area and vi.indicator = 'B' )
select record, version, indicator, area from vista where area IN ( select area from vista where indicator = "B" )