CODEWITHSUNDEEP
mysql
Jimmy
Jimmy

Reputation: 79

MySQL Select User id based on multiple AND conditions on same table

I want to select a userid from a single table based on multiple and condition.

UserID   FieldID  Value
-----------------------------------
1        51         Yes 
1        6          Dog 
2        6          Cat
1        68         TX  
1        69         78701
2        68         LA

What I'm trying to get in simple words:

if user search for texas or 78701, Select userId where (68 = TX OR 69=78701) AND (51=yes) AND (6=Dog)

This should return user id 1.

This is what I tried, but returns null.

SELECT user_id FROM `metadata` 
WHERE ( (`field_id` = '68' AND value LIKE '%TX%') 
   OR (`field_id` = '69' AND value LIKE '%78701%') ) 
   AND `field_id` = '51' AND value = 'Yes'
   AND `field_id` = '6' AND value = 'Dog'

Upvotes: 1

Views: 1262

Answers (4)

Ponni
Ponni

Reputation: 433

SELECT user_id FROM metadata WHERE (field_id = '68' AND value LIKE '%TX%') OR (field_id = '69' AND value LIKE '%78701%') AND (field_id = '51' AND value = 'Yes') AND (field_id = '6' AND value = 'Dog');

I have little bit changed your query and tried with the same,it gives output as, user_id is 1

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

This approach is typically slower than simply LEFT JOINing that table on each criterion, but it can make the problem simpler to comprehend...

SELECT userid
     , MAX(CASE WHEN fieldid = 51 THEN value END) smoker
     , MAX(CASE WHEN fieldid =  6 THEN value END) favourite_pet
     , MAX(CASE WHEN fieldid = 68 THEN value END) state
     , MAX(CASE WHEN fieldid = 69 THEN value END) zip
  FROM eav
 GROUP
    BY userid;

You can use HAVING, or bundle this into a subquery to get the desired results.

Upvotes: 0

StuartLC
StuartLC

Reputation: 107267

Your table structure resembles attribute+value modelling, which essentially splits up the columns of a row into individual pairs, and has the side effect of very weak typing.

As you've noted, this can also make things tricky to query, since you have to reason over multiple rows in order to make sense of the original data model.

One approach could be to take an opinion of a 'primary' criterion, and then apply additional criteria by reasoning over the shredded data, joined back by user id:

SELECT DISTINCT m.user_id 
FROM `metadata` m
WHERE ((`field_id` = '68' AND value LIKE '%TX%') 
       OR (`field_id` = '69' AND value LIKE '%78701%'))
 AND EXISTS 
 (SELECT 1 
    FROM `metadata` m2 
    WHERE m2.user_id = m.user_id AND m2.field_id = '51' AND m2.value = 'Yes')
AND EXISTS 
  (SELECT 1 
     FROM `metadata` m3 
     WHERE m3.user_id = m.user_id AND m3.field_id = '6' AND m3.value = 'Dog');

However, IMO, it may be better to attempt to remodel the table like so (and ideally choose better descriptions for the attributes as columns):

UserID   Field51  Field6 Field68  Field69
----------------------------------------
1        Yes      Dog     TX      78701
2        No       Cat     LA      NULL

This will make things much easier to query.

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use GROUP BY with a HAVING clause that makes use of multiple conditional aggregates:

SELECT UserID
FROM metadata
GROUP BY UserID
HAVING SUM(field_id = '68' AND value LIKE '%TX%' OR
           field_id = '69' AND value LIKE '%78701%') >= 1

       AND

       SUM(field_id = '51' AND value = 'Yes') >= 1

       AND

       SUM(field_id = '6' AND value = 'Dog') >= 1

Demo here

Explanation: In MysQL a boolean expression, like

field_id = '51' AND value = 'Yes'

returns 1 when true, 0 when false.

Also, each predicate of HAVING clause is applied to the whole group of records, as defined by GROUP BY.

Hence, predicate:

SUM(field_id = '51' AND value = 'Yes') >= 1

is like saying: return only those UserID groups having at least one (>=1) record with 

field_id = '51' AND value = 'Yes' -> true

Upvotes: 3

Related Questions