My table is defined like this:
Name is a string and property too.
ID | Name | Property
An example for data in this table is this:
ID | Name | Property
1 Peter Newsletter
2 Paul Register
3 Peter Register
4 Shaun Newsletter
5 Steve Register
Now I like to query all people that have the property newsletter and register. As a result I should get Peter, because he has both property's.
So the resulting table should be like:
ID | Name | Property
1 Peter Newsletter
3 Peter Register
So everything I try to query is which person has both property's newsletter and register.
Here is one method:
select t.*
from table t
where exists (select 1
from table t2
where t2.name = t.name and t2.property = 'NewsLetter'
) and
exists (select 1
from table t2
where t2.name = t.name and t2.property = 'Register'
);
If you just want the list of names, perhaps with ids, I would do that as:
select t.name
from table t
where t2.property in ('NewsLetter', 'Register')
group by t.name
having count(distinct property) = 2;
How you get the list of ids depends on your database, something like listagg() or group_concat() or string_agg().
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments