create table test(
nombre varchar2(20),
posicion varchar2(20));
insert into test values ('juan', 'defensa');
insert into test values ('juan', 'medio');
insert into test values ('jaime', 'defensa');
insert into test values ('alberto', 'medio');
--select nombre from test where posicion ='defensa' or posicion='medio';
block quote
-->this returns me if they have one or the other position but I want to know which record satisfies both values, 'defense' and ',middle'. Thank you
You can group the results by name and keep only those that have two positions within the selected ones: