CODEWITHSUNDEEP
mysqlsql
shadow_of__soul
shadow_of__soul

Reputation: 427

where clause matching 2 rows in the same table to get results

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.

Upvotes: 0

Views: 82

Answers (3)

Jackie Robinson
Jackie Robinson

Reputation: 94

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 )

Upvotes: 0

lc.
lc.

Reputation: 116468

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'

Upvotes: 1

Kermit
Kermit

Reputation: 34055

You're saying that profile_key should be 1 and 3. This is impossible.

You need to use an OR, not AND.

SELECT user_id 
FROM   table 
WHERE  ( profile_key = 1 && profile_value REGEXP 'test1' ) OR ( 
       profile_key = 3 && profile_value REGEXP 'test4' )

Upvotes: 1

Related Questions