Question 114378 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Filter out data within a single query

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?

Tags:

3

Answers


  1. 0 
    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.

    Login or Signup to reply.
  2. 0

    You can use HAVING:

    select email
from t
group by email 
having min(type) = 'type2' and max(type) = 'type2'
    Login or Signup to reply.
  3. 0

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

    sqlize

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search