Question: i have a search query that i need to modify and adapt into a custom profile system we have, that system use the following table:

```
  profile_key | profile_value        | user_id
       1      |    test1             |  10
       2      |    test2             |  10
       3      |    ["test3","test4"] |  10

```

i need to add to the where clause something that would match all the rows (depending of what the user defined in the search form) to get the user\_id, something like:

```
 select user_id from table where (profile_key = 1 && profile_value regexp 'test1') && (profile_key = 3 && profile_value regexp 'test4')

```

i need to get all the user\_id IF it matched all the defined profile\_key and the regexp.

any idea how i can accomplish this?

Regards.

Answer: 
The simplest way would be to use `EXISTS`:

```
SELECT user_id
FROM users
WHERE EXISTS (SELECT 1 FROM profiles WHERE profile_key = 1 
    AND profile_value regexp 'test1' AND profiles.user_id = users.user_id)
AND EXISTS (SELECT 1 FROM profiles WHERE profile_key = 3 
    AND profile_value regexp 'test4' AND profiles.user_id = users.user_id)

```

You could also accomplish this with an `INNER JOIN`, once for each row you want to match:

```
SELECT user_id
FROM users
INNER JOIN profiles p1 ON users.user_id = p1.user_id
INNER JOIN profiles p2 ON users.user_id = p2.user_id
WHERE p1.profile_key = 1 AND p1.profile_value regexp 'test1'
AND p2.profile_key = 3 AND p2.profile_value regexp 'test4'

```
What about using an 'IN', something like this

```
select user_id 
from table 
where (profile_key = 1 && 'test1' IN profile_value) 
    && (profile_key = 3 && 'test4' IN profile_value )

```