-1

Need help getting a SQL. I have below kind of table.

-----

Need below result records based on the fact that they have "abc" and "def" in Field2

  • just one occurrence of each value not more
  • no other value apart from "abc" and "def".

Expected result

---

Records that should not be in result enter image description here

I have tried different experiments with

Select * from MYTABLE
WHERE FIELD1 in ('abc','def')
Group by Field1
Having Count(*) = 2

but am doing something fundamentally wrong here, as it gives me "Member4" also since it has two occurrences of "abc"

GMB
  • 216,147
  • 25
  • 84
  • 135
PMDEV
  • 15
  • 4

2 Answers2

1

You can use conditional aggregate expressions in the having clause:

select field1
from mytable
group by field1
having
        sum(case when field2 = 'abc' then 1 else 0 end) = 1
    and sum(case when field2 = 'def' then 1 else 0 end) = 1
    and sum(case when field2 not in ('abc', 'def') then 1 else 0 end) = 0
GMB
  • 216,147
  • 25
  • 84
  • 135
1

Most databases support string aggregation. That can be the simplest method. For instance, in MySQL syntax:

select field1
from t
group by field1
having group_concat(field2 order by field2) = 'abc,def';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786