I have the following data in the booking table.
booking
id email price type areaid
1 [email protected] 70 type1 1
2 [email protected] 60 type2 2
3 [email protected] 50 type1 3
4 [email protected] 110 type1 3
5 [email protected] 90 type2 4
6 [email protected] 65 type2 1
7 [email protected] 84 type2 2
8 [email protected] 84 type1 2
I need to retrieve all email addresses from booking table which have only type2 and no other types. According to the data, only person2 meets this requirement.
How can I achieve this within a single query?
CodePudding user response：
You can use HAVING:
select email
from t
group by email
having min(type) = 'type2' and max(type) = 'type2'
CodePudding user response：
Select b.email
from booking b
group by b.email
having
'type2'= any(array_agg(b.type)) and array_length(array_agg(b.type),1)=1;
You can use above query for your task. First group by clause using email then use having clause to filter the results after get grouped.
CodePudding user response：
One more query for this problem is simple translation English to SQLish:
select distinct email
from test
where type = 'type2'
and not exists (
select email from test t2 where test.email = t2.email and t2.type != 'type2'
);