CODEWITHSUNDEEP
sql
Roostir
Roostir

Reputation: 1

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

Upvotes: 0

Views: 49

Answers (3)

Neeraj Agarwal
Neeraj Agarwal

Reputation: 1059

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

Upvotes: 0

Victor P
Victor P

Reputation: 1612

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

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

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

Upvotes: 1

Related Questions