For example, this is my response of
select *
from x
where id = 1
Result:
ID data
1 mouse
1 england
1 computer
Now, how do I search for a mouse, in the country England? I can't really check with this:
AND data = 'mouse'
AND data = 'england'
(Rather not use a query in a query if possible)
If I understand correctly, you have a "set-within-sets" query. You want to find all three attributes for a given id. I recommend using group by and having for this purpose:
select id
from x
where data in ('mouse', 'england')
group by id
having count(*) = 2;
Use subquery to constrain the country. For example for this data
ID DATA
---------- --------
1 computer
1 england
1 mouse
2 austria
2 computer
2 mouse
3 mouse
3 mouse
you first filters only IDs that belongs to England and than constrains the Mouse.
SELECT *
FROM test
WHERE id IN
(SELECT id FROM test WHERE data = 'england'
)
AND data = 'mouse';
ID DATA
---------- --------
1 mouse
