sql - How to query for values that share another value in common -

sql - How to query for values that share another value in common -

greenplum 4.2.2.4 (like postgresql 8.2) on server.

i have info like:

id | user ------+------ 12345 | bob 12345 | jane 12345 | mary 44455 | user1 44455 | user2 44455 | user3 67890 | bob 53756 | bob 53756 | bob 53756 | bob 25246 | jane 54383 | jane 54383 | jane 54383 | jane

i want homecoming rows "id" shared multiple unique "user" values. however, i'm querying based on list of "user" values interested in. example:

where user in ('mary','bob','user2')

i want query return:

id | user ------+------ 12345 | bob 12345 | jane 12345 | mary 44455 | user1 44455 | user2 44455 | user3

how can this?

you can using window functions:

select id, user (select t.*, min(user) on (partition id) minuser, max(user) on (partition id) maxuser table t ) t minuser <> maxuser;

edti: without window functions (which thought around since postgres 8.1 trust erwin on matter), can same thing join , group by:

select t.id, t.user table t bring together (select user, min(user) minuser, max(user) maxuser table t grouping user having min(user) <> max(user) ) tu on t.user = tu.user;

sql postgresql greenplum