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';

Expected results:

RECORD  VERSION INDICATOR AREA
1234    01  A     CA
1234    02  B     CA
1234    03  C     CA

3 answers

  • answered 2019-08-24 00:15 sticky bit

    You can use EXISTS and a correlated subquery that checks for a record with a 'B' indicator.

    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');
    

  • answered 2019-08-24 00:17 Victor P

    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'
    )
    

  • answered 2019-08-24 00:17 Neeraj Agarwal

    select record, version, indicator, area
    from vista
    where area IN
     (
     select area
     from vista
     where indicator = "B"
     )