How to select people who has multiple values-CodePudding

I want to select people who has 2 values (activate & recurring) in the table for example,

table :: tbl_transactions

id	name	action
1	John	activate
2	John	recurring
3	Salah	activate
4	Bill	activate
5	Bill	recurring
6	Bill	recurring

Expected result,

id	name	action
1	John	activate
2	John	recurring
4	Bill	activate
5	Bill	recurring
6	Bill	recurring

Please help. I have been spent for an hour to fix this.

Really thanks.

CodePudding user response：

You can aggregate the action values for each user name and check if the array is 2 long (since you only need 2 actions) and contains ['activate', 'recurring'] (since you only need these values)

SELECT t.id, t.name FROM tbl_transactions t
JOIN LATERAL (
    SELECT 
        name, 
        ARRAY_AGG(action) AS actions
    FROM tbl_transactions
    GROUP BY name
) user_actions ON t.name = user_actions.name 
                  AND ARRAY_LENGTH(actions, 1) = 2
                  AND ARRAY['activate', 'recurring']::VARCHAR[] @> actions

Demo

CodePudding user response：

here is a query for selecting only names appearing twice:

select t.* 
from t 
  join (select name, count(*) 
        from t 
        having count(*) = 2
       ) c on t.name = c.name ;

CodePudding user response：

The query will be as below. Check Sql Fiddle. You can use count distinct action to count distinct action value check it is greater than 1.

select a.* from tbl_transactions a join 
(select name, count(*) from tbl_transactions group by name having count(distinct action) > ) b 
on a.name = b.name

CodePudding user response：

I would use the bool_or() window function for this:

with complete_check as (
  select id, name, action,
         bool_or(action = 'recurring') over w as has_recurring,
         bool_or(action = 'activate') over w as has_activate
    from tbl_transactions
  window w as (partition by name)
)
select id, name, action
  from complete_check
 where has_recurring and has_activate;

db<>fiddle here